Split a cell

[Spraggs]

Hello All,
I have a list of several thousand cells all similar to the text below.

1234-GSV-BLD-ADM-BD009 BUILDING - OFFICE BLOCKS

There is always 8 spaces in the middle but can start from different posiition, hence I can't use a simple LEFT & MID function.

So I require a formula for B2 & C2 etc.
B2 1234-GSV-BLD-ADM-BD009
C2 BUILDING - OFFICE BLOCKS

Thanks Jase

 

[VoG]

Try

=LEFT(A2,FIND(" ",A2)-1)

and

=TRIM(SUBSTITUTE(A2,B2,""))

 

[texasalynn]

does this work?

Excel Workbook
	A	B	C
1	1234-GSV-BLD-ADM-BD009 BUILDING - OFFICE BLOCKS	1234-GSV-BLD-ADM-BD009	BUILDING - OFFICE BLOCKS
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|")))
C1	=RIGHT(A1,FIND("|",SUBSTITUTE(A1," ","|"))+1)

 

[p45cal]

try:

Excel Workbook
	A	B	C
2	1234-GSV-BLD-ADM-BD009 BUILDING - OFFICE BLOCKS	1234-GSV-BLD-ADM-BD009	BUILDING - OFFICE BLOCKS
Sheet9

in the formulae above there are supposed to be 8 spaces between the double quotes; Excel Jeanie or MrExcel removed them for me!

 

[p45cal]

trying again:

Excel Workbook
	A	B	C
2	1234-GSV-BLD-ADM-BD009 BUILDING - OFFICE BLOCKS	1234-GSV-BLD-ADM-BD009	BUILDING - OFFICE BLOCKS
3	1234-GSV-BLD-ADM-BD009 BUILDING - OFFICE BLOCKS	1234-GSV-BLD-ADM-BD009	BUILDING - OFFICE BLOCKS
Sheet9

 

[texasalynn]

my formula's also had 8 spaces in the substitute portion

=LEFT(A1,FIND("|",SUBSTITUTE(A1,"        ","|")))