I have a problem: Say I have letters and numbers mixed together in a column, and I just want the letters before the numbers...
alp144 alp
city123 city
mom344 mom
tha233a tha
tlc234 tlc
vacpm234a vacpm
Is this even possible? Thanks in advance for any help.
Matt
Try this
=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)
where your value is in A1. Does that work for you?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Okay, thanks. That works. Can you explain this formula? I would be interested to know how it works...
If you're speaking of the one I presented
=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)
Okay, breaking it apart somewhat, The FIND Function will find the position of a given value and return that position. (We don't know yet exactly what value is in the first numerical position yet).
The LEFT function takes everything to the left of a named position (in this case the value found by FIND -1.
The combination of MIN and FIND will return the lowest value found in that FIND Array [1,2,3,....}. However, if one of those digits that the FIND function is looking for is not there (for example alp144alp does not contain 0,2,3,5,6,7,8 or 9), the equation will return an error. To avoid that, we concatinate 1234567890 to the end of A1 in the equation so that we know that all digits will be represented (A1&1234567890).
So for alp144alp, the formula would simplify to
LEFT("alp144alp",MIN(FIND({1,2,3,4,5,6,7,8,9,0},"alp144alp123456789"0-1)
LEFT("alp144alp",MIN(4, 11,12,5,14,15,16,17,18,19)-1
LEFT("alp144alp",3)
Clear as mud now?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks