Extract String

[billandrew]

Good morning

Trying to extract a string of numbers, could be up to 20 values which are separated by a column into individual cells in same row. Formula would be beneficial.

Example

1, 2, 3, - export as 1 2 3 separated.

Thank you

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G	H
13	1, 2, 3, 4, 5, 6, 7	1	2	3	4	5	6	7
extract

Cell Formulas
Range	Formula
B13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")
C13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")
D13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")
E13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")
F13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")
G13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")
H13	=IF(TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$A13,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")

 

[billandrew]

Thank you sir

 

[billandrew]

My data is in a diiferent cell. When I adjust, nothing is returned.

 

[Rick Rothstein]

My data is in a diiferent cell. When I adjust, nothing is returned.

It would have helped if you had told us what cell your data started in. Since you didn't, I'll assume cell E11... this formula in cell F11 and copy it across for as many cells as you think you will ever have data for, then copy those down to the bottom of your data...

=TRIM(MID(SUBSTITUTE(","&SUBSTITUTE($E11," ",""),",",REPT(" ",99)),COLUMNS($F:F)*99,99))

NOTE 1: I am assuming none of your cells will contain more than 99 characters

NOTE 2: Change the column references (highlighted in red) to the column you put the formula in.

 

[Scott T]

Can you post a sample of your data and the exact formula you used.

 

[billandrew]

The cell with the data is AI5. Currently looks like 1, 2, 5, 6

The formula is - =IF(TRIM(MID(SUBSTITUTE(","&$AI5,",",REPT(" ",200)),200*(COLUMN()-1),200))<>"",TRIM(MID(SUBSTITUTE(","&$AI5,",",REPT(" ",200)),200*(COLUMN()-1),200))+0,"")

 

[billandrew]

Mr. Rothstein

Adjusted to my cell references, works perfect.

I don't entirely understand how it works though.....

Thank You

 

[jtakw]

Hi,

Try this:

Excel 2010
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS
5	1, 2, 3, 4, 5, 6, 7, 8, 9, 10	1	2	3	4	5	6	7	8	9	10
Sheet6

Cell Formulas
Range	Formula
AJ5	=IFERROR(MID(SUBSTITUTE(","&$AI5,",",REPT(" ",100)),COLUMNS($B:B)*100,100)+0,"")

Formula copied across as needed, use formula as is.

 

[billandrew]

This works perfect as well.

What is the Columns Function doing?