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?

this should help

=TEXT(MID(J5,1,LEN(J5)-1),"000-000-000")&RIGHT(J5,1)

--
Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!


"57chevy" wrote:

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?

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).



In article ,
57chevy wrote:

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?

=LEFT(A1,3)&"-"&MID(A1,4,3)&"-"&RIGHT(A1,LEN(A1)-6)
--
David Biddulph

"57chevy" wrote in message
...
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?