Extract House Number from Street Address into new Cell

I'm converting an KML file (Google Earth XML file) to a CSV format that I can use with my GPS. Excel is great to help me map the XML fields to where I need them. One of the fields is a description field, which in my case, contains the actual street address and phone number of a particular point of interest.

I'm using excel to break this description field up into individual cells using the text to columns function, but it's far from perfect, as not all addresses or cells are formated the same, so it's somewhat manually intensive.

The one thing I haven't been able to figure out is how to extract the house number from a street address. There are a few entries that are missing a house number, so I simply can't use text to columns and use a space as a deliminator. Also, that splits up the remaining part of the address into multiple columns as well, which I want to keep intact.

Is there a way I can use the ISNUMBER function and LEFT together to extract this value?

Thanks for any advise you can give me.

Originally Posted by NBVC

Can you give a few examples that represent all your addresses?

Kinkaid Road
185 Chappie James Blvd
6530 Taylor Ave

this is by excel formula:
=left(a1,find(" ",a1,1)-1)

This is with VBA

Please Login or Register  to view this content.

Originally Posted by NBVC

Can you give a few examples that represent all your addresses?

Originally Posted by daddylonglegs

...and this variation on that will give you "No number" where one doesn't exist

=IF(ISERR(LEFT(A1)+0),"No number",LEFT(A1,FIND(" ",A1)-1))

[assumes number will always be at the start or not at all]

That doesn't seem to be working. It may be a version issue. I'm runing Excel 2010 if that makes a difference.

Originally Posted by daddylonglegs

The formula I suggested should work in any version.

In what way does it not work - can you show some examples of the data you have and the results you get which are wrong?

Excel isn't taking the forumula at all. When I paste it in, it spreads it across three cells. When I type it in, it just treats it as text. I suspect something is incorrect in the formatting. Trying to figure it out.

Originally Posted by daddylonglegs

Somtimes that can happen if you copy the formula from here and inadvertently include the blank line above or below.

I tested the formula again just now and it works OK for me - can you try copying again from here or just type the formula in....

I don't know why, but it won't peform the function. It is typed in there, but won't run.

=IF(ISERR(LEFT(D2)+0),"NO NUMBER",LEFT(D2,FIND(" ",D2)-1))

Originally Posted by tamorgen

I don't know why, but it won't peform the function. It is typed in there, but won't run.

=IF(ISERR(LEFT(D2)+0),"NO NUMBER",LEFT(D2,FIND(" ",D2)-1))

Okay, just got it to work. I had to go to the formula tab, while the formula was highlighted, selected insert function, then clicked okay, and it worked.

Now, here's the other part of the question. Now that the House number is seperated, how do I take everything after that number and put it in it's own cell?

Hello, I have a similar request regarding a VBA script.

I have lots of data in (all rows belonging to) "Column A" contain alphanumeric text along with spaces, dashes, and commas. I need two pieces of data extracted from each row, and pasted into two other consecutive rows.

The first data piece I need to extract is a dollar amount (with the dollar sign if possible) usually containing pennies to two decimals places.

Example in Cell A7:
Jack owes us $101.24
I would need the amount $101.24 to be extracted/copied to Cell J7.

The second piece I need to extract is an ID number which is made up of either 1 letter and 1 number, or 2 letters.

Example in Cell A9:
James owes us $200, and his section ID# is 9s
I would need the ID# 9s to be extracted to Cell K9.

Example in Cell A11:
Joey owes us $150, and his section ID# is Ks
I would need the ID# Ks to be extracted to Cell K11.

Example picture:

http://oi56.tinypic.com/10f5o2s.jpg

I appreciate any help on this matter Thanks!