Split number and get element that start from specific numbers

Question

I have the following row in Excel. I need to split it get only the phone number that contains '+357 99' or '+357 97'

Phone number
+357 22333333, +357 22444444
+357 97222222
+357 22000000, +357 22111111, +357 99000000

For example the new resulted row should be the following:

Phone number

+357 97222222
+357 99000000

Currently I only have =SPLIT(B2,",").

Can you help me please?

What happens in the output should a given row have more than one match, or would that never be expected? — Tim Biegeleisen, Dec 29 '20 at 08:10
Are all numbers followed by a comma? As the 3rd and 4th are not. If the data is consistent, then you could use find() and iferror()... — Solar Mike, Dec 29 '20 at 08:16
@SolarMike Only if more that one number is available then the comma is being there. — zinon, Dec 29 '20 at 13:34

score 2 · Answer 1 · answered Dec 29 '20 at 08:22

You can use the below formulas to find the numbers

for +357 97 (Enter this formula in B2 Cell)

=IFERROR(MID(A2,SEARCH("+357 97",A2),13),"")

for +357 99 (Enter this formula in C2 Cell)

=IFERROR(MID(A2,SEARCH("+357 99",A2),13),"")

Excel Snip

Drag the formula to the bottom cell to get all results. you can also concatenate the results to one column if you want

score 2 · Accepted Answer · answered Dec 29 '20 at 08:35

If it would be possible to have more than a single match, you may try:

Formula in B2:

=TEXTJOIN(", ",,IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[starts-with(., '+357 99') or starts-with(., '+357 97')]"),""))

If you don't want to use TEXTJOIN() but you need to split the results, you could try:

=TRANSPOSE(IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A2,", ","</s><s>")&"</s></t>","//s[starts-with(., '+357 99') or starts-with(., '+357 97')]"),""))

If you don't have Excel O365, you can use:

=IFERROR(INDEX(FILTERXML("<t><s>"&SUBSTITUTE($A2,", ","</s><s>")&"</s></t>","//s[starts-with(., '+357 99') or starts-with(., '+357 97')]"),COLUMN(A1)),"")

Drag left and right.

Note FILTERXML() is available from Excel 2013 onwards.

If only a single value of both excists, the other answer will handle just fine. Also, unfortunately "SPLIT()" is not an Excel function, but if you are interested in how FILTERXML() can be used to split a string, you may find this interesting.

score 1 · Answer 3 · answered Dec 29 '20 at 08:37

I think FILTERXML() will best fit in this case. Use start-with in xPath parameter of FilterXML() function.

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,", ","</s><s>")&"</s></t>","//s[starts-with(., '+357 99') or starts-with(., '+357 97')]"),"No Match")

An excellent explanation of FILTERXML() function in this post from JvdD

Split number and get element that start from specific numbers

3 Answers3