insert a "-"

[tpkelley_no]

I have a excel work sheet that I exported. in column "D" i have cost codes. for example 00-105, 00-600. some of them do not have a dash. for example 01600, 05300. What would be the fasted was in adding a "-" when missing?

 

[KennyGreens]

Re: incet a "-"

Is it always going to be 00-? Or will some numbers be for example 01- or 02-, etc.?

 

[Ingolf]

The codes are always in that format XXYYY ?

 

[tpkelley_no]

Re: incet a "-"

they will always have two numbers first. the numbers can be 01 thur 99. i want the format to be XX-XXX

 

[Ingolf]

Re: incet a "-"

Is it always going to be 00-? Or will some numbers be for example 01- or 02-, etc.?

OP specified..

for example 01600, 05300.

 

[Peter_SSs]

Re: incet a "-"

Are you looking for ..
- a manual method to do this via the keyboard/mouse?
- a formula to produce the correct codes in another column?
- a macro to fix the original data?

 

[BarryL]

Re: incet a "-"

Can you try the below formula ( i put in E2) and copy it down

=IF(NOT(ISNUMBER(SEARCH("-",D2))),LEFT(D2,2)&"-"&RIGHT(D2,3),D2)

 

[tpkelley_no]

Re: incet a "-"

They have to many to do it by hand. how would a formula fix the issue? what would it be?

 

[Peter_SSs]

Re: incet a "-"

how would a formula fix the issue? what would it be?

Barry has given you one suggestion. Here is another one.

Excel Workbook
	D	E
1
2	00-105	00-105
3	00-600	00-600
4	01600	01-600
5	05300	05-300
Insert -

 

[Ingolf]

Re: incet a "-"

try:

=REPLACE(A2,3,1,"-"&MID(A2,3,1)) assuming that string is in A2