locate second space in a text string

[tersus]

Hi

I have an address field and i need to split into 2 columns, but i need to do this at the 2nd space i.e.

flat 21 the avenue

i need column A= flat 21 and column B= the avenue

has anyone got a formula that will do this (no macros please i am not that clever)

 

[ayazgreat]

why are you not trying text to columns option in excel ?

 

[VoG]

Try this

Excel Workbook
	A	B	C
1	Flat 21 The Avenue	Flat 21	The Avenue
Sheet1

 

[tersus]

Thank you

 

[Peter_SSs]

Welcome to the MrExcel board!

Another option for cell B1 with VoG's layout is ..
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)
.. but I'd have to say you will be luckier that most if all your addresses are as uniform in structure as that.

 

[tersus]

Hi

thank you, and no my address list is not that simple, and your help will be part of a much larger field mapping issue i have to get my head around, this was just the first hurdle, thanks again

 

[VBABEGINER]

Hi Vog Sir and Peter Ss..

Thanks for posting this. This helped me a lot..formula. Absolutely great.

Welcome to the MrExcel board!

Another option for cell B1 with VoG's layout is ..
=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)
.. but I'd have to say you will be luckier that most if all your addresses are as uniform in structure as that.