Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String
When you are faced with alphanumeric strings, such as those esoteric-looking serial numbers that represent a store’s stock items, here is how you can deal with parsing them based on the position of their first integer.
To find the position of the first integer, the formula in cell B2 and copied down to cell B13 is
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))
Notice for example in cell B2, the formula returns 3. That is because cell A2 contains the alphanumeric string GS5453GDGD5. The first integer in that string is 5 and it is the third character (that is, in the third position) of that string.
To extract everything to the left of the first-found integer, the formula in cell C2, copied to cell C13 is
=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)
A final example, to extract all characters to the right of — while including — the first found integer, the formula in cell D2 and copied down to cell D13 is
=TRIM(REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,""))
Thank you for the post, minor comment re: “To find the position of the first integer”
this appears to need a check for no numbers found else returns len(A2)+1
Here is a quick version to return zero if no numbers are found:
=IF(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))>LEN(A2),0,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″)))
Well, you raise a valid point in the sense that there is the assumption that the value of interest would always be alphanumeric. In my mind, I envisioned model numbers or part numbers that, to a company’s internal system, would be mandated as containing a number and letter before being accepted. Therefore, in that scenario, no verification would need to take place.
However, as I said, you raise a valid point in cases when it’s possible for integers to not be present. I would not design a project where the formula I posted was not meant for such a circumstance; I’d mandate an alphanumeric string as I said above. But to your point, I also see that an empty cell will return the number 1. So with a possible 1 or (in your example) a zero, both of which are integers and potential misleading return values, I’d return a string instead, such as “No integers”.
Thank you for your follow up formula and for noticing the shortcoming. I’ve made a note of it here.
Thank you for your reply Tom.
You bring up an excellent point of understanding and communicating your requirements, by mandating at least one numeric value will be present in the source string you’re able to implement a cleaner and faster solution using just a single call to “MIN(FIND(…”.
Regarding your comments of returning the string “No integers” I would still lean towards returning zero given the expectation of an integer return value and I would consider 0 valid as the number of integers found when there are no integers in the string. Of course, either approach would work fine as long as expectations are communicated. : )
=IF(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″))>LEN(A2),0,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&”0123456789″)))
Finds position of digit. How would you find the value of that digit please?
Try this array formula, and because it is an array formula, make sure you commit it to the cell with Ctrl+Shift+Enter, not just with Enter.
=MID(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT(“1:100”)),1)),0),100-SUM(1*ISERROR(1*MID(A2,ROW(INDIRECT(“1:100”)),1))))*1