Forum Discussion
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)
Presuming your text is in A2, you might use:
=LEFT(A2,MIN(IFERROR(SEARCH(CHAR(SEQUENCE(26,,65,1)),A2),""))-1)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")Much has changed in two years.
An even easier solution:
=REGEXEXTRACT(A1,"^\d+")^ -indicates to start at the first character in the string
\d - any digit
+ is added to indicate "1 or more digits"
- 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)))
- This worked, thank you!
- Glad it worked. Have a great weekend!
