Separating Numbers from Text

[John Smith]

Is there a formula or function to separate a number (street number) from an
address and show this in another column

So if I have 4217 Jones Street in Cell A1 can I put 4217 into Cell b1 and
Jones Street into Cell c1

Many thanks for your help in advance

Stephen West
Gold Coast, Australia

 

[Domenic]

Hi Stephen,

Try,

B1=LEFT(A1,FIND(" ",A1)-1)
C1=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

Hope this helps!

 

[Don Guillett]

If you want formulas
=LEFT(G1,FIND(" ",G1)) to get 4217
=RIGHT(G1,LEN(G1)-FIND(" ",G1)) to get Jones Street

 

[Aladin Akyurek]

B1:

=LOOKUP(9.99999999999999E+307,--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))

C1:

=TRIM(SUBSTITUTE(A1,LEFT(A1,LEN(B1)),""))

 

[John Smith]

Unusual response

I typed 14 Martin Place in cell A1 and pasted in the =lookup to B1 and =
trim to c1
The responses I got:
B1 38060
C1 rtin Place
A little confused!
Regards
Stephen

 

[Barbara]

Unusual response

I typed 14 Martin Place in cell A1 and pasted in the =lookup to B1 and =
trim to c1
The responses I got:
B1 38060
C1 rtin Place
A little confused!
Regards
Stephen

It's because for excel, (unfortunally!), "14 mar" is a date and then a
number value.
Aladin's formula gives the last number value (last for position) in the
array:
--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1))))
that is, with your example:
{1\14\14\#VALUE!\#VALUE!\38060\#VALUE!\#VALUE!\#VALUE!\#VALUE!\#VALUE!\#VALU
E!\#VALUE!\#VALUE!\#VALUE!}
and so it gives: 3860, last number.

Anyway, Aladin had got a great idea, in my opinion.

To solve this problem, trying to follow Aladin's idea, I found nothing
better than:
=INDEX(--LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))),MATCH(TRUE,ISERROR(--LEFT(A1,R
OW(INDIRECT("1:"&LEN(A1))))),)-1)
(array formula)
but in this form... it's not so interesting... and surely Aladin will do
much better.

Regards
Barbara

 

[Aladin Akyurek]

Great test case that you come up with...

The LEFT bit computes the following array...

{"1";"14";"14 ";"14 M";"14 Ma";"14 Mar";"14 Mart";"14 Marti";"14 Martin";"14
Martin ";"14 Martin P";"14 Martin Pl";"14 Martin Pla";"14 Martin Plac";"14
Martin Place"}

The item, "14 Mar", coerced by the -- bit, gives a serial date which is a
number and it is the last numeric value in the coerced array:

{1;14;14;#VALUE!;#VALUE!;38060;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALU
E!;#VALUE!;#VALUE!;#VALUE!}

LOOKUP picks up, as it should, that last numeric value. Hence the unusual
response.

The following modification rescues the formula (temporarily) from its
demise:

=LOOKUP(9.9999999E+307,-LEFT("-"&A1,ROW(INDIRECT("1:"&LEN(A1)))))

Note that the -- bit is reduced to just a minus.

Thanks for testing.

 

[John Smith]

....And you wouldn't believe it! Martin Place was just the first street name
that came to mind!
I have the sort happening and I appreciate (once again) the group input
Thanks from Australia!