To split numbers and characters

[Ecel Dumbo]

Hi Friends,
Could you please suggest two formula that will give me numbers only in one column and characters in another column
20001 SYDNY-BRIDGE
2002 SYDNY-CHINA
203 SYDNY_WYN
20700000 BROADWAY

there are spaces before the characters
thanks heaps

 

[vijay.vizzu]

It seems, you have one space between number and Character. So you can use Text To Columns option to make it. Numbers in one column and text in another column.. Please try

 

[vijay.vizzu]

Please have a look on this

http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/

 

[Hui]

Number: =LEFT(A1,FIND(" ",A1)-1)
Text: =SUBSTITUTE(RIGHT(A1,LEN(A1)-LEN(C1))," ","")

 

[XLPadawan]

In C1: =LEFT(A1,FIND(" ",A1)-1) <==extend down
In D1: =MID(A1,FIND(" ",A1)+1,LEN(A1)) <==extend down

 

[Ecel Dumbo]

Thanks Hui and XL Padawan

 

[Ecel Dumbo]

Thanks vijay, text to columns will work too. just fascinated with formulae