Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String

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,""))

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
6 comments on “Tom’s Tutorials For Excel: Finding the Position of the First Integer in an Alphanumeric String
  1. Ken says:

    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

  2. Ken says:

    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″)))

    • Tom Urtis says:

      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.

  3. Ken says:

    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. : )

  4. Peter says:

    =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?

    • Tom Urtis says:

      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

Leave a Reply

Your email address will not be published.

*