Search string for alpha or numeric
 

I have a text string that looks something like this: "| |- - - Whatever" or
"|- | - - 51250". I'd like to remove the leading characters by searching for
the first alpha or numeric value then possibly using a Mid function to take
everything to the right of that alpha or numeric. What functions can be used
to accomplish this?

Try using the SEARCH function to find the location of the end of the text
string and use a MID from there.

"David" wrote:

I have a text string that looks something like this: "| |- - - Whatever" or
"|- | - - 51250". I'd like to remove the leading characters by searching for
the first alpha or numeric value then possibly using a Mid function to take
everything to the right of that alpha or numeric. What functions can be used
to accomplish this?

Try this:

=MID(A1,MIN(SEARCH({"0","1","2","3","4","5","6","7 ","8","9","A","B","C","D","E","F","G","H","I","J", "K","L","M","N","O","P","Q","R","S","T","U","V","W ","X","Y","Z"},UPPER(A1)&"0123456789ABCDEFGHIJKLMN OPQRSTUVWXYZ")),LEN(A1))

Does that work?
--
Regards,
Ron


"Barb R." wrote:

Try using the SEARCH function to find the location of the end of the text
string and use a MID from there.

"David" wrote:

I have a text string that looks something like this: "| |- - - Whatever" or
"|- | - - 51250". I'd like to remove the leading characters by searching for
the first alpha or numeric value then possibly using a Mid function to take
everything to the right of that alpha or numeric. What functions can be used
to accomplish this?

Formula correction...

This may seem trivial, but SEARCH is not case sensitive, so the UPPER
function is not necessary:

=MID(A1,MIN(SEARCH({"0","1","2","3","4","5","6","7 ","8","9","A","B","C","D","E","F","G","H","I","J", "K","L","M","N","O","P","Q","R","S","T","U","V","W ","X","Y","Z"},A1&"0123456789ABCDEFGHIJKLMNOPQRSTU VWXYZ")),LEN(A1))
--
Regards,
Ron

You just saved me a few hours of pulling my hair out. Both I and my barber
thank you!

"Ron Coderre" wrote:

Try this:

=MID(A1,MIN(SEARCH({"0","1","2","3","4","5","6","7 ","8","9","A","B","C","D","E","F","G","H","I","J", "K","L","M","N","O","P","Q","R","S","T","U","V","W ","X","Y","Z"},UPPER(A1)&"0123456789ABCDEFGHIJKLMN OPQRSTUVWXYZ")),LEN(A1))

Does that work?
--
Regards,
Ron


"Barb R." wrote:

Try using the SEARCH function to find the location of the end of the text
string and use a MID from there.

"David" wrote:

I have a text string that looks something like this: "| |- - - Whatever" or
"|- | - - 51250". I'd like to remove the leading characters by searching for
the first alpha or numeric value then possibly using a Mid function to take
everything to the right of that alpha or numeric. What functions can be used
to accomplish this?