Separating Name and Address with No Delimiter

**lizzae** · 04-22-2009, 11:46 AM

Hi!
I have a spreadsheet with over 3000 rows.
There is a column in the sheet that contains name and address, combined. My goal is to separate out the name into one column and the address into the other. Since there is no real delimiter between the two and since there is no fixed number of words in the string, I am having trouble finding an answer. Can someone please help or possibly offer a macro that might do the job? Below are a few examples of the text string:

AARON W BENT 13 SLETH LOW MOUNTAIN ROAD RIDGFELD CT 06877
ABE SMITH 3585 STONEWYCK PL MONROW TWP NJ 08831-2671
ABE DOE 2189 SCHOOL ROAD WEST HEMPSTEAD NY 11552
LISA NAYMARK 198-4 ROMEO CT HOLLYWOOD NY 11423-1309

I am somewhat experienced in excel but definitely not an expert.
Thank you, thank you thank you!!

**Special-K** · 04-22-2009, 12:00 PM

This may help

http://www.eggheadcafe.com/conversat...eadid=30753295

**lizzae** · 04-22-2009, 12:36 PM

Thank you for the link.
However, I'm not familiar with code or anything involving VB.
I can cut and paste code into a macro but as far as understanding what it says, i'm completely lost.
I was hoping someone might be able to provide a macro or a more specific formula that might do the job?

**martindwilson** · 04-22-2009, 03:37 PM

if all your cells have name followed by number namexxxxxx 123 blah blah
asuming first is in a1
then in b1
=TRIM(LEFT(A1,FIND(MID(A1,MIN( FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1),A1)-1))
in c1
=MID(A1,FIND(MID(A1,MIN( FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),1),A1),255)
dragged down