Extracting 7-digit number from text string

Question

Hi,&nbsp;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.

djclements · Answer

Syed_001&nbsp;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.

syed_001 · Answer

Hi&nbsp;djclements,These are dummy sample of the text.- XXXXXX7209 MOHD SYE BIN OMAITR 1004925 JSC6896 RFD-&nbsp;XXXXXX4020 HONG KONG ISLAND IND AB 1005049&nbsp;The number will always be between 1000000 to 2000000. The position of the number varies.

djclements · Answer

Syed_001&nbsp;There are a few different approaches you could take, depending on if certain conditions are always true...&nbsp;If the text strings will only ever contain ONE numeric value per string (ie: "1004925" is numeric, while&nbsp;"XXXXXX7209" and "JSC6896" are not), you could use:&nbsp;=SUM(IFERROR(--TEXTSPLIT(C3, " "), 0))&nbsp;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:&nbsp;=MAX(IFERROR(--TEXTSPLIT(C3, " "), 0))&nbsp;Alternatively, if the number you require will always be the minimum numeric value in the string, you could use MIN, but replace 0 with "":&nbsp;=MIN(IFERROR(--TEXTSPLIT(C3, " "), ""))&nbsp;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:&nbsp;=LET(arr, IFERROR(--TEXTSPLIT(C3, " "), 0), FILTER(arr, (arr&gt;=1000000)*(arr&lt;=2000000), ""))&nbsp;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:&nbsp;=LET(arr, IFERROR(--TEXTSPLIT(C3, " "), 0), TEXTJOIN(", ", TRUE, FILTER(arr, (arr&gt;=1000000)*(arr&lt;=2000000), "")))&nbsp;

sergeibaklan · Answer

Syed_001&nbsp;
Assuming that's the only number in the text which has 7 characters length, that could be
=FILTERXML("&lt;t&gt;&lt;s&gt;"&amp;SUBSTITUTE(A1," ","&lt;/s&gt;&lt;s&gt;")&amp;"&lt;/s&gt;&lt;/t&gt;", "//s[number()=. and string-length()=7 ]")
or
=FILTERXML("&lt;t&gt;&lt;s&gt;"&amp;SUBSTITUTE(A1," ","&lt;/s&gt;&lt;s&gt;")&amp;"&lt;/s&gt;&lt;/t&gt;", "//s[.*0=0 and string-length()=7 ]")

&nbsp;

peiyezhu · Answer

=WEBSERVICE("http://e.anyoupin.cn/eh3/?preg_match~(?&lt;=\D)\d{7}(?=\D)~" &amp; A2)

Forum Discussion

Extracting 7-digit number from text string

5 Replies

Resources