Insert Characters (Excel 2003 )

Topic

New Reply

I have string, eg. TH0000005678 in a range, let say in column A, how do I
insert “-” after the first 2 characters and before the last character
resulting TH-000000567-8. The string are always 12 Characters.

Thanks in advance

regards, francis

Reply | Quote

Replies

If the values start in cell A1:
– Enter the following formula in B1:

=LEFT(A1,2)&"-"&MID(A1,3,9)&"-"&RIGHT(A1,1)

– Fill down as far as needed.
– If you want to replace the original values:
– Select the cells in column B.
– Press Ctrl+C.
– Select Edit | Paste Special…
– Select the Values option and click OK.
– Delete column A.

Reply | Quote

Hi Hans and Nathan

Thanks for the formulas, both works well

cheers, francis

Reply | Quote

Edit: But Hans is both more experienced and quicker at typing than me

I would do a concatenate in the adjacent cell, for example in cell B1 (for A1):

=CONCATENATE(LEFT(A1,2),”-“,MID(A1,3,9),”-“,RIGHT(A1,1))

Copy down, replace formulas with values and replace old data with new.

HTH

Reply | Quote