Remove everything in a Cell to the right of the first number, including that num

[Trevasaurus]

I'm trying to figure out how to Remove all characters in a column, starting from, and including, the first number in the cell.

Example:

Column range B2:B101, find first number in each cell, delete everything to the right in that cell, including that number.

I've tried a couple ways to do this but I can't figure it out.

Can anyone help?

Thanks,
Trevor

[alansidman]

Are you able to post some sample data, so that we can see what the information looks like.



Edit: Look at this post from earlier today
http://www.excelforum.com/excel-form...-sequence.html

[Trevasaurus]

Of course, why didn't I think of that. Thanks for the advice. Example:

A
Symbol
A2 GC2Z
A3 GC3G
A4 GC4F
A5 AGC5Y
A6 BBC8W

The Desired Outcome would be:

A
Symbol
A2 GC
A3 GC
A4 GC
A5 AGC
A6 BBC

[FDibbins]

try this monster, copied down...
=LEFT(A1,SEARCH(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))),A1,1)-1)

[Trevasaurus]

Wow, that works great. Thanks so much.

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)