Extracting Everything LEFT of First Number

[cutter7900]

Good Afternoon,

I’ve been struggling with an Excel 2010 sheet and I’ve finally come to the conclusion that I have to reach out for help. In the attached example, I am trying to extract just the name of the individual at the address listed. Sometimes the address is on the second line, other times the address is combined with the name on the first line. As you can see from the formula in column B, I’m trying to use the LEFT FIND command. I want the formula to extract everything to the left of the first numerical character found. For some reason the array is not working and the formula only extracts everything to the left of the first number of the array, which in this case is "0". It Extracts everything but the last section of the Zip Code in B1, and everything left of the street address is B2 because it encounters a "0". What needs to happen in order to get the formula to extract everything left of the first number encountered? Thanks in advance,

[daffodil11]

How about:

=LEFT(A1,MIN(IF(ISNUMBER(FIND({0;1;2;3;4;5;6;7;8;9},A1)),FIND({0;1;2;3;4;5;6;7;8;9},A1)))-2)

[PeteABC123]

Try this in B1 and copy down:

Please Login or Register  to view this content.

Please let us know if it worked for you.

Pete

[cutter7900]

Great guys!!! BOTH of those solutions worked. If you have the time I'd be interested in a breakdown on what those formulas are saying. Why didn't my original array work?

[daffodil11]

Buildin on Pete's amazing magical formula:

Line 2!

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1,FIND(CHAR(10),A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")))

Seriously Pete, that was awesome.

[cutter7900]

I got about halfway through that and my nose started bleeding.

Thanks for your expertise, gents.

[Tony Valko]

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

Just because I'm "different"...

...A1&"0123456789"

If you put the 0 after the 9 then you can eliminate the quotes:

...A1&1234567890