CODEWITHSUNDEEP
excel
firmo23
firmo23

Reputation: 8404

Add symbol to specific part of a string in excel

I have a column in excel which includes phone numbers. I want to apply a formula which will add "-" after the first 3 digits only in the phone numbers with 7 digits. The rest should remain the same.

212-528-1108                     212-664-3087                     631-204-7931                    
       [4] 6464717                     718-896-1730                     718-781-6500                    
       [7] 646-654-5880                     2122069                     202-445-8150                    
      [10] 212-996-5501                     516-526-4459                     516-984-4315

Upvotes: 0

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

We can try using LEN() here along with concatenation:

=IF(LEN(A1)=7, LEFT(A1, 3)&"-"&RIGHT(A1, 4), A1)

Note that if you also need to do validation on a value with 7 characters, e.g. to assert that they are all numbers, then we would have to do more work than this. I am assuming that you know that all values in the columns are some type of phone number.

Upvotes: 1

Related Questions