Merging info from 5 cells to 1

[Guest]

I have a number 21-/A/-525-/E/-0001 the / represent the cells, I want it in
one cell to look like 21-A-525-E-0001. We I concatenate or use & it removes
the dashes and turns 0001 into 1. Does anyone have any suggestions?

 

[y_not]

Stupid suggestion but have you tried formatting the cell as "Text"?
don't know if this will work - just a thought

Good luck

Ton

 

[Guest]

Yes I tried that but when I do it removes the dashes and the o's

 

[jbrackett]

If the last set is always 4 chars:

=A1&"-"&B1&"-"&C1&"-"&D1&"-"&(REPT(0,4-LEN(E1))&E1)

A B C D E FORMULA (RESULT)

1 21 A 525 E 1 21-A-525-E-0001

2 68 B 525 E 21 68-B-525-E-002

 

[Guest]

I think your problem is the cells with numbers have some wierd custom
formats. If this is the case you can use a formula like this

=TEXT(A1,"#-")&B1&TEXT(C1,"-#-")&D1&TEXT(E1,"-0000")

 

[Guest]

Wow that's graet except that the last 4 always show as 4 Zeros

 

[Guest]

Thanks that worked perfectly

I think your problem is the cells with numbers have some wierd custom
formats. If this is the case you can use a formula like this

=TEXT(A1,"#-")&B1&TEXT(C1,"-#-")&D1&TEXT(E1,"-0000")

 

[Dave Peterson]

maybe:

=A1&"-"&B1&"-"&C1&"-"&D1&"-"&text(e1,"0000")