Split the number from an adress ? (ie. Road of Liberty 17)

1. Split the number from an adress ? (ie. Road of Liberty 17)

Hi,

I have tons of adresses formatted in one cell like this: "Road of Liberty 14",
I need 1 cell for the adress and 1 for the house number
The problem with splitting is that there are very often spaces in the adress, so Excel will split it the following way: "Road" "of" Liberty" "14"...
I couldn't figure out how to simply extract all numbers out of the text and paste it in the next cell.
Since it is a huge database and I will have to repeatedly do it again I would prefer not to do it manually.
Thanks in advance for any help !

Simon

2. Hi,

Welcome to the forum, you could use Data > Text to columns and use delimited and select how many columns you require

3. can you give some more example addresses.
can the address be high street 14a or is it always a number at the end?
what is the maximum length of the number?

I am thinking as per the example you want each address spilt in 2?
Is it always the last space in a string tthat is the point it should be split?

Regards

Dav

4. well perhaps if the address is in a1

in b1
``Please Login or Register  to view this content.``
and in c1
``Please Login or Register  to view this content.``
Does that help

Regards

Dav

5. Thanks for the answer, first solution doesn't work for me because the addresses don't have a fix lenght, their format varies very much.
In the few cases that the housenumber would be 15a, or similar I can manually change it... but the house numbers can be 1, 17 or 119.
And yes, it is the last space where it should be split!
I cannot directly copy your formula Dav, because my excel is german, but I will try to decipher the logic behind it, thanks !
I would appreciate any new answers as well !

6. Here's an alternative if you need one

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)-1)))

House number

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",10)),10))

7. Thanks, I have succesfully translated these 2 formulas into german but it turns up with #name?
Any help with that ?

8. does this help?

Regards

Dav

9. Excellent, that helps indeed, have been trying for an hour or so to solve this problem now, seems like a lot of folks on the internet have a similar problem!
Thanks again!

Simon

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1