Forum Discussion
Rachwar30
Sep 02, 2022Copper Contributor
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!
Presuming your text is in A2, you might use:
=LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)
- PeterBartholomew1Silver Contributor
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!
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) ) ) );
- Patrick2788Silver Contributor
Presuming your text is in A2, you might use:
=LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)
- CourtneyTXCopper 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?
CourtneyTX , slightly modified Patrick2788 formula
=IFNA( CONCAT( REGEXEXTRACT( A1,"\d+",1) ), "no digits")
- Chris1Copper ContributorThis will fail if the string is only numeric, correct?
- Patrick2788Silver Contributor
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.
- Rachwar30Copper ContributorThis worked, thank you!
- Patrick2788Silver ContributorGlad it worked. Have a great weekend!