To split numbers and characters

[Excel 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

[zbor]

Look here:
http://www.excelforum.com/excel-gene...ml#post3770803

Just add TRIM function in front of SUBSTITUTE

[Punnam]

Hi,

My solution will work if u have "Space" after ending of number .

Punnam

[zbor]

Mine will work with or without space

[Punnam]

@ zbor ,

Can u plz explain what is happening in details for this
=LOOKUP(99^99,--LEFT(A3,ROW($A$3:$A$6)))
It will be more helpful to us
Punnam

[martindwilson]

=LOOKUP(99^99,--LEFT(A3,ROW($A$3:$A$6))) is not right
=LOOKUP(99^99,--LEFT(A3,ROW($A$1:$A$100)))
or
maybe
=LOOKUP(99^99,--LEFT(A3,ROW($1:$100)))
the row bit needs to start at 1 and be at least as long as the text in a3
use the evaluate formula option from the formula tab to see what is happening

[Punnam]

@ martindwilson you are right the formula shows me error i have change the range, i have done it to understand the logic .

Please correct me if am wrong
The lookup function is searching for a number in a range & when it is not in the array showing the last possible value in array

Punnam

[martindwilson]

yep thats correct

[Punnam]

Thanks for clarification

Punnam

[zbor]

LOOKUP function return last value that is not a error and lower than lookup value.
Since lookup value is very huge number (99^99 = 3,6973E+197) probably all numbers you use are lower than this (if not biggest number in excel is 9.99999999999999E+307 so it would be best to use that number but I'm lazy typing).

For example, from range

1
1
#N/A
3
2
#N/A

formula

=LOOKUP(100,B5:B10)

will return 2 (latest number in range).

LEFT function take strings from LEFT.
ROW function evaluate array ROW(A1:A100) into 1,2,3,4....,98,99,100 and it will take 1st, 2nd,3rd..., 99th, 100th value from the left.
Note: That's why it always start from 1st row, no matter where is data actually so you need to lock range if you moving formula around ROW($A$1:$A$100)

From string 45453Punnam it will evaluate this:

4
45
454
4545
45453
45453P
45453Pu
45453Pun
45453Punn
45453Punna
45453Punnam

-- will convert this text into number so you will get:

4
45
454
4545
45453
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!

Since last number is 45453 that's your desired result.

Not that from 4545A3432432423423421534534Punnam
It will return 4545 and if this is not your expected result then you need to change approach

[Excel Dumbo]

Thank you very much ZBor, punnam and martin.