How to split numbers separated by semicolons in a cell?

[Sparty2319]

I have a number string in cell A1 that looks something like this:

12; 34; 56; 78; 90

I would like to extract each number into cells A2, A3, A4, A5, and A6, respectively. After extracting, cell A2 should contain 12, A3 34, and so on.

Thank you very much for your help.

 

[jtakw]

Hi,

A2 formula converts result to Real Numbers
B2 formula results as Text, in case you have leading zeros you need retained:

Book3.xlsx
	A	B
1	12; 34; 56; 78; 90
2	12	12
3	34	34
4	56	56
5	78	78
6	90	90
7
8
Sheet891

Cell Formulas
Range	Formula
A2:A8	A2	=IFERROR(MID(SUBSTITUTE(";"&A$1,";",REPT(" ",100)),ROWS(A$2:A2)*100,100)+0,"")
B2:B8	B2	=TRIM(MID(SUBSTITUTE(";"&A$1,";",REPT(" ",100)),ROWS(B$2:B2)*100,100))

 

[kweaver]

Select A1, then do Text to Columns using ; as the delim and placement starting in B2.
But that will put the results in B2:F2, however.
So, you could then select those cells, copy, and with the A2 cell selected, paste special transpose.

 

[jtakw]

@kweaver , think OP want the results down column, in separate rows, rather then across column, in one row.

 

[kweaver]

jtakw: right, just modified my post as did you.

 

[Rick Rothstein]

If you could have a lot of numbers in cell A1 and were willing to use a macro, this one would work...

Sub SplitDownCellA1()
  [A2:A1000] = Application.Transpose(Split([A1] & String(999, ";"), ";"))
End Sub

Note
----------------------
As written, this code will handle up to a maximum of 999 delimited numbers in cell A1. Also note that you do not have to clear the cells below cell A1 if you change the value in cell A1 as the macro will do that automatically.

 

[Peter_SSs]

You can also do this with a single formula in a single cell.
If the numbers are all 2 digits like your example then just use the formula show in A2. The other results will automatically 'spill' down the required number of cells.
If the numbers can vary in length, then use the formula in B2. Again the other results will automatically spill down

21 04 03.xlsm
	A	B
1	12; 34; 56; 78; 90	15; 88; 256; 2356
2	12	15
3	34	88
4	56	256
5	78	2356
6	90
7
Split values

Cell Formulas
Range	Formula
A2:A6	A2	=MID(A1,SEQUENCE(5,,,4),2)+0
B2:B5	B2	=FILTERXML("<p><c>"&SUBSTITUTE(B1,"; ","</c><c>")&"</c></p>","//c")
Dynamic array formulas.

 

[Kenneth Hobson]

As a UDF with string in A1, use formula commented and drag down. Put UDF in a Module.

'=IFERROR(field($A$1,ROW()-1),"")
Function Field(cell, pos As Integer, Optional delimit As String = ";")
  Dim a() As String
  a() = Split(cell, delimit)
  Field = a(pos - 1)
End Function