Text to 4 columns

B

billinr

I have a column which contains values formatted to be separated by a space.
There are only going to be 4 values; I would like to move them into 4
different columns.
An example of the expression would be "253569 568J8 EJ139 Started"
I realize I can do a text to column function, but I wanted to build a
formula because this will be repeated every time the sheet is used.

The numbers are going to be in column "B", and I would like to separate them
into "C", "D", "E" and "F".

Can anyone provide an example?

TIA
 
M

Max

Source data in A1 down

In B1:
=LEFT(A1,SEARCH(" ",A1)-1)
In C1:
=MID(A1,SEARCH(" ",A1)+1,SEARCH(" ",A1,SEARCH(" ",A1)+1)-SEARCH(" ",A1))
In D1:
=MID(A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1,SEARCH(" ",A1,SEARCH("
",A1,SEARCH(" ",A1)+1)+1)-SEARCH(" ",A1,SEARCH(" ",A1)+1))
In E1:
=RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(" ",A1,SEARCH(" ",A1)+1)+1))

Copy B1:E1 down as far as required

B1 returns as text. If you need it as a number,
use instead in B1: =LEFT(A1,SEARCH(" ",A1)-1)+0
 
M

Max

Oops, missed that bit about the source data in col B.
Adjust all 4 formulas accordingly to point to B1 instead of A1
 
Top