How do I add dashes to a string?

G

Guest

I have a column of thext in Excel that is

111222333
or
111222333L

And want to convert it so that it reads

111-222-333
or
111-222-333L

I have applied "=TEXT(A1, "000-000-000")" and have the format
"111-222-333" but when the original string has an "L" on the end, it's not
included. How can I reformat and preserve the L when it is in the original
string?
 
J

JE McGimpsey

One way:


=IF(ISNUMBER(A1),TEXT(A1,"000-000-000"),LEFT(A1,3)&"-"&MID(A1,4,3)&"-"&MI
D(A1,7,255))


(where 255 is just a big number - any number 4 or greater will work).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding lines together to create one line 4
key coulm 2
#REF! error when deleting rows 2
Help me parsing Data and Organizing 1
Combine two sets of overlapping Date/Time data into 3 columns. 3
Column to text 2
Highlighting reaccuring info in a spreadsheet 2
Sql to find record in a hierarchical chain 5

Top