howto read phone numbers from country & city code

[andrielau]

I've repeatedly made changes to the formula but it still has not worked, can someone here who can help ?

https://prnt.sc/giouqy
https://prnt.sc/gip11e
https://prnt.sc/gioxl2
https://prnt.sc/giower
https://prnt.sc/gioyet

Thank you

[CK76]

Posting image is good start. But it would be much better to post sample workbook with data and your expected result.

Detailing what sheet contains raw data and where you want the end result.

Upload sample workbook by going to "Go Advanced", then to Manage attachment in Additional Options.

[andrielau]

Posting image is good start. But it would be much better to post sample workbook with data and your expected result.

Detailing what sheet contains raw data and where you want the end result.

Upload sample workbook by going to "Go Advanced", then to Manage attachment in Additional Options.

Ok this is sample workbook

[oeldere]

c2 =VLOOKUP(LEFT(A2,4),Main!$F$1:$G$15000,2,0) and drag down.

I could not check the result since you did not added it.

[CK76]

LOOKUP isn't ideal for this. As it's approximate match.

Either use VLOOKUP as oeldere showed.

Or INDEX,MATCH
=INDEX(Main!$G$2:$G$13226,MATCH(LEFT(A2,4),Main!$F$2:$F$13226,0))

[andrielau]

thank you i really appreciate this solution but there is problem when country & city code have 5 digits because thereis 4 - 7 digits. so the problem is "LEFT(A2,4)" when i change it to LEFT(A2,5) there is no error with 5 digits code but back again if code only 4 digits so i must change it back to "LEFT(A2,4)"

[CK76]

It'd help if you can give more samples that illustrate your issue.

Giving samples for as many different patterns that could be found in your actual data.

Without it, it's very likely that we will give you formula that satisfy only the sample provided.

From your description, I'd guess VBA and RegEx may be faster/easier route than formula. Are you comfortable with VBA solution?

Edit: Come to think of it, it may not be that simple even when using VBA. See link.
https://en.wikipedia.org/wiki/Nation...ephone_numbers

Basically, there's different phone number pattern for various countries that you need to accommodate for.

[andrielau]

thank you for all comment, i just make modification formula and it's work when i test it start from 3 until 7 country & city code and no error. i hope it's final

=IF(ISNA(VLOOKUP(LEFT(A2,3),Main!$F$1:$G$13226,2,0)),IF(ISNA(VLOOKUP(LEFT(A2,4),Main!$F$1:$G$13226,2,0)),IF(ISNA(VLOOKUP(LEFT(A2,5),Main!$F$1:$G$13226,2,0)),IF(ISNA(VLOOKUP(LEFT(A2,6),Main!$F$1:$G$13226,2,0)),IF(ISNA(VLOOKUP(LEFT(A2,7),Main!$F$1:$G$13226,2,0)),"",VLOOKUP(LEFT(A2,7),Main!$F$1:$G$13226,2,0)),VLOOKUP(LEFT(A2,6),Main!$F$1:$G$13226,2,0)),VLOOKUP(LEFT(A2,5),Main!$F$1:$G$13226,2,0)),VLOOKUP(LEFT(A2,4),Main!$F$1:$G$13226,2,0)),VLOOKUP(LEFT(A2,3),Main!$F$1:$G$13226,2,0))