SOLVED

# Extracting numbers before string characters start

Copper 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

8 Replies
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Extracting numbers before string characters start

Presuming your text is in A2, you might use:

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

# Re: Extracting numbers before string characters start

This worked, thank you!

# Re: Extracting numbers before string characters start

Glad it worked. Have a great weekend!

# Re: Extracting numbers before string characters start

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!

# Re: Extracting numbers before string characters start

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

# Re: Extracting numbers before string characters start

This will fail if the string is only numeric, correct?

# Re: Extracting numbers before string characters start

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.

# Re: Extracting numbers before string characters start

@Patrick2788 Or we could use the NOT of the NOT approach to define what we want to keep and hence not restrict the exclusion to just capital letters:

numbers before:

``````=LET(in,A1,
nums, SEQUENCE(10,,0),
noNums, INDEX(TEXTSPLIT(in, nums,,1),1),
TEXTBEFORE(in,noNums,1))``````

all numbers:

``````=LET(in,A1,
nums, SEQUENCE(10,,0),
noNums, TEXTSPLIT(in, nums,,1),
CONCAT(TEXTSPLIT(in,noNums)))``````
1 best response

Accepted Solutions
best response confirmed by HansVogelaar (MVP)
Solution

# Re: Extracting numbers before string characters start

Presuming your text is in A2, you might use:

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