Forum Discussion
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 aft...
Presuming your text is in A2, you might use:
=LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)
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!
- This will fail if the string is only numeric, correct?
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.
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)))
