Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search string with multiple criteria | Excel Worksheet Functions | |||
How do I search for a string across multiple worksheets in Excel? | Excel Worksheet Functions | |||
Numeric cell but shows as string? | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) | |||
Q: search in string | Excel Discussion (Misc queries) |