Remove Only The Number Portion Of An Address Record In Excel Cell

**MAGIC_INC** · 12-20-2012, 08:38 PM

I have a database of 70,000 business clients, incl. pick-up and delivery addresses for our shipping business. We're moving to a new Cloud based shipping software system that requires us to separate the street # from the street name in different columns. I found a post here to help me isolate just the street # from the address cell and copy it over to another column. Hurdle #1 Conqured - Thank you Excel Forum!

Hurdle #2: I need to (either) remove the just the street number portion from the address record in the cell -OR- copy all the street name and suite info after the street # to another cell. The street number records vary substantially from 2-10 digits or more, but they always start with the # in the cell on the left. Long story short...

currently (just 1 record) e2=4943 MC CONNELL AVENUE

I would like to accomplish this somehow:

e2=MC CONNELL AVENUE
d2=4943

Have already figured out how to get the street # copied thanks to this forum. If someone has some thought on how I can just wipe the Street # out of the cell, I would greatly appreciate the help.

Thx!

**Richard Buttrey** · 12-20-2012, 08:42 PM

Hi,

One way would be

=LEFT(E2,FIND(" ",E2)-1)

**MAGIC_INC** · 12-20-2012, 11:28 PM

Richard,

I do have this formula, but thank you so much! It does work great to get the street number copied out of the address record column into its own column. I am using it now to achieve this task. Very clean and easy!

I am looking for a formula that will remove the street number entirely from the current cell containing the address string. Our new database strings are formatted as separate columns: StreetNumber , StreetName.

It seems I cannot find the tools or formula to (either) omit the street number entirely from the current cell, or to copy everything (after) the street number and get it into its own column (void of the street number). I have to somehow get this done for the 70,000 records I have.

I am sure it is possible I just lack the skills,

Any help I could muster on this would be greatly appreciated.

Magic

**Richard Buttrey** · 12-21-2012, 06:40 AM

Hi,

Why not use the first formula I gave you to get the number part in one helper column, then use Copy and Paste Special Values to convert these formulae to values. Then use the similar

=RIGHT(E2,LEN(E2)-FIND(" ",E2))

formula to extract the street name in a second helper column. Then if necessary copy the whole of the second helper column and use Paste Special Values back to the original column.