SOLVED

Extracting numbers before string characters start

New Contributor

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!

5 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@Rachwar30 

Presuming your text is in A2, you might use:

 

=LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)
This worked, thank you!
Glad it worked. Have a great weekend!

@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!

 

@Peter Bartholomew 

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