Forum Discussion

Rachwar30's avatar
Rachwar30
Copper Contributor
Sep 02, 2022
Solved

Extracting numbers before string characters start

Hi all,

 

I am looking for an excel formula that can help me to extract numbers only from a mix of numbers and characters ONLY before the first character starts (i.e sometimes there are numbers after letters but I dont want those numbers).

 

Example:

I want the following:

4400B-E

5148A1-E

7006B3-E

 

To look like this:

4400

5148

7006

 

Thank you in advance!

  • Rachwar30 

    Presuming your text is in A2, you might use:

     

    =LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)
  • Rachwar30 

    Or, just because I find it difficult ...

    WorksheetFormula
    = SelectNumλ(@target);
    
    SelectNumλ 
    = LAMBDA(t, [s],
         LET(
            n, LEN(t),
            x, LEFT(t, 1),
            y, RIGHT(t, n - 1),
            d, ISNUMBER(VALUE(x)),
            IF(d, SelectNumλ(y, s & x), s)
         )
      );

    a recursive Lambda!

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP

      PeterBartholomew1 

      In general optional parameter is not required, something like

      getNumber=
      LAMBDA( str,
          IF( ISERROR( --LEFT(str,1) ), "",
              LEFT(str,1) &
              getNumber( RIGHT( str, LEN(str) - 1) )
              ) );
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor

    Rachwar30 

    Presuming your text is in A2, you might use:

     

    =LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)
    • CourtneyTX's avatar
      CourtneyTX
      Copper Contributor

      Patrick2788I just used this to help me extract numbers from a difficult text string today! I'd love to understand how these functions are working together. Can you explain?

    • Chris1's avatar
      Chris1
      Copper Contributor
      This will fail if the string is only numeric, correct?
      • Patrick2788's avatar
        Patrick2788
        Silver Contributor

        Chris1 

        This formula satisfies the OP's original request. It's a bit smaller than the previous solution:

        =LET(
            letters, CHAR(SEQUENCE(26, , 65)),
            1 * TEXTBEFORE(A2, letters, , , , A2)
        )

         

        This formula pulls all numbers from a string, regardless of position:

        =LET(
            n, LEN(A2),
            arr, 1 * MID(A2, SEQUENCE(n), 1),
            1 * TEXTJOIN(, , TOCOL(arr, 2))
        )

         

        If you need something different, you could create a new discussion.

         

Resources