Text to 4 columns
 

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

Text to 4 columns
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"billinr" wrote:

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

Text to 4 columns
 

Oops, missed that bit about the source data in col B.
Adjust all 4 formulas accordingly to point to B1 instead of A1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"billinr" wrote:

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

Text to 4 columns
 

Perfect solution - as usual!

Thanks for your help.

"Max" wrote:

Oops, missed that bit about the source data in col B.
Adjust all 4 formulas accordingly to point to B1 instead of A1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---


"billinr" wrote:

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

Text to 4 columns
 

Welcome, and thanks for the feedback.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"billinr" wrote in message
...
Perfect solution - as usual!

Thanks for your help.