Separating Numbers from Text

  • Thread starter Thread starter John Smith
  • Start date Start date
J

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
 
Hi Stephen,

Try,

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

Hope this helps!
 
If you want formulas
=LEFT(G1,FIND(" ",G1)) to get 4217
=RIGHT(G1,LEN(G1)-FIND(" ",G1)) to get Jones Street
 
B1:

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

C1:

=TRIM(SUBSTITUTE(A1,LEFT(A1,LEN(B1)),""))
 
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
 
John Smith said:
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
Click to expand...
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
 
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.
 
....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!
 
Back
Top