Forum Discussion
Extracting 7-digit number from text string
Hi,
I have a bank statement that needs recon. I need to extract number ranging from1000000 to 2000000 from a column. Is there a formula or ways to extract the specific number range into a different column.
Syed_001 If possible, could you please share an example of a complete text string from which you want to extract the number? An appropriate solution may depend on the other text present in the string, as well as the location of the number within the string. Use "dummy" data in your example, so you're not sharing any sensitive information, but please be sure to follow the same format as your real data.
Hi djclements,
These are dummy sample of the text.
- XXXXXX7209 MOHD SYE BIN OMAITR 1004925 JSC6896 RFD
- XXXXXX4020 HONG KONG ISLAND IND AB 1005049
The number will always be between 1000000 to 2000000. The position of the number varies.
Syed_001 There are a few different approaches you could take, depending on if certain conditions are always true...
If the text strings will only ever contain ONE numeric value per string (ie: "1004925" is numeric, while "XXXXXX7209" and "JSC6896" are not), you could use:
=SUM(IFERROR(--TEXTSPLIT(C3, " "), 0))However, this method runs the risk of adding multiple numbers together, if there's a possibility of two or more actual numbers appearing in a single string. If that's the case, but the number you require will always be the maximum value, you could replace SUM with MAX:
=MAX(IFERROR(--TEXTSPLIT(C3, " "), 0))Alternatively, if the number you require will always be the minimum numeric value in the string, you could use MIN, but replace 0 with "":
=MIN(IFERROR(--TEXTSPLIT(C3, " "), ""))If none of the above scenarios are always true, you could use the LET function to define the array of values resulting from TEXTSPLIT, then FILTER the array by the appropriate conditions:
=LET(arr, IFERROR(--TEXTSPLIT(C3, " "), 0), FILTER(arr, (arr>=1000000)*(arr<=2000000), ""))Lastly, if there's still a chance that multiple numeric values may appear in a single string, between 1000000 and 2000000, you may also want to use TEXTJOIN to return all matching values in a delimited string:
=LET(arr, IFERROR(--TEXTSPLIT(C3, " "), 0), TEXTJOIN(", ", TRUE, FILTER(arr, (arr>=1000000)*(arr<=2000000), "")))
