Merging numbers with semicolon separation

[jlugo]

HI

Here's my challenge:

#1 - Digits must retain 4 digit value
#2 - I need to merge them into one row separated by semicolon ";"

[TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]0028[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]0298[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]0357[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]0387[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]0524[/TD]
[/TR]
</TBODY>[/TABLE]

DESIRED GOAL - 0028;00298;0357;0387;0524

How do I do this w/out use of VBA code?

 

[Michael M]

Assuming the data is in col"A"
Try

=A1&";"&A2&";"&A3&";"&A4&";"&A5

 

[jlugo]

If have 93 columns of 4 digit #s. I may consider a macro if there isn't a fast way to drag to the end.

 

[JoeMo]

If have 93 columns of 4 digit #s. I may consider a macro if there isn't a fast way to drag to the end.

Here's a fast way to drag to the end. Drag formula in B2.

Excel Workbook
	A	B
1
2	0028	0028
3	0298	0028;0298
4	0357	0028;0298;0357
5	0387	0028;0298;0357;0387
6	0524	0028;0298;0357;0387;0524
Sheet4

 

[jlugo]

Thank you Michael!

Thank you Joe! This dragging down only works until the 53rd row. All other rows from 54th row to 93rd show #VALUE! error.

 

[JoeMo]

Thank you Michael!

Thank you Joe! This dragging down only works until the 53rd row. All other rows from 54th row to 93rd show #VALUE! error.

Sorry, forgot that concatenate function limits the number of arguments. Anyway, it's not needed. Try this instead:

=IF(B1="",A2,TEXT(B1,"0000")&";"&TEXT(A2,"0000"))

and drag down

 

[jlugo]

Hi Joe,

I tried this and results added some odd zeros. First formula was closest to what I was looking for.
Execute and see for yourself comes out odd. I appreciate your help.

 

[JoeMo]

Hi Joe,

I tried this and results added some odd zeros. First formula was closest to what I was looking for.
Execute and see for yourself comes out odd. I appreciate your help.

I started with your posted data with the numbers custom formatted as "0000". Below is with last formula I posted and column B right aligned.

Excel Workbook
	A	B
1
2	0028	0028
3	0298	0028;0298
4	0357	0028;0298;0357
5	0387	0028;0298;0357;0387
6	0524	0028;0298;0357;0387;0524
Sheet4