extract the data from a text string at each space using formulae

[jbesr1230]

Hello,

I would like to extract the data from a text string at each space using formula rather than text-to-column tool. the text will have 4 interior spaces and a mixture of text and numbers.

For example, in cell A10 will be: abcdef 04/13/2018 140.50 ghiab hgvjw
I would like B10 to contain: abcdef
I would like c10 to contain: 04/13/2018
I would like d10 to contain: 140.50
I would like e10 to contain: ghiab
I would like f10 to contain: hgvjw

thank you.

 

[njimack]

Excel Workbook
	A	B	C	D	E	F
9		1	2	3	4	5
10	abcdef 04/13/2018 140.50 ghiab hgvjw	abcdef	04/13/2018	140.50	ghiab	hgvjw
Sheet3

 

[jtakw]

Hi,

Here's another way:

Excel 2010
	A	B	C	D	E	F
10	abcdef 04/13/2018 140.50 ghiab hgvjw	abcdef	04/13/2018	140.50	ghiab	hgvjw
Sheet4

Cell Formulas
Range	Formula
B10	=TRIM(MID(SUBSTITUTE(" "&$A10," ",REPT(" ",100)),COLUMN(A10)*100,100))

B10 formula copied across.

 

[Peter_SSs]

Hi,

Here's another way:

Excel Workbook
10	abcdef 04/13/2018 140.50 ghiab hgvjw	abcdef	04/13/2018	140.50	ghiab	hgvjw
Excel 2010

<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B10</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">" "&$A10," ",REPT(<font color="Purple">" ",100</font>)</font>),COLUMN(<font color="Green">A10</font>)*100,100</font>)</font>)</td></tr></table></td></tr></table>

B10 formula copied across.

I would be a little wary of using that exact formula. If any new columns are subsequently inserted before column A, incorrect results will be returned.

This modification, in B10 copied across to F10, would be more robust in that regard.

Excel Workbook
	A	B	C	D	E	F
10	abcdef 04/13/2018 140.50 ghiab hgvjw	abcdef	04/13/2018	140.50	ghiab	hgvjw
Split Text 1

If you are happy to have individual formulas for each column rather than "one-size-fits-all-columns", then you could consider these, copied down if required.

Excel Workbook
	A	B	C	D	E	F
10	abcdef 04/13/2018 140.50 ghiab hgvjw	abcdef	04/13/2018	140.50	ghiab	hgvjw
Split Text 2

 

[azumi]

Another option, put in B10 and copied cross

=TRIM(MID(SUBSTITUTE($A10," ",REPT(" ",LEN($A10))),(COLUMNS($A$1:A1)-1)*LEN($A10)+1,LEN($A10)))