Phone Number Validation -> REGEXMATCH for digits?

[MagellanCapital]

Hi,
I'm looking to make an excel formula based on the following logic. Preferably not scripting etc. I am trying to validate numbers for a phone dialer.


In column (a) I have phone numbers (no special characters)
in column (b) I have the correct country code for that number's country

IF (a) starts with 0, drop the 0. Lets call that (a-0)
IF (a-0) starts with (b), print (a-0)
ELSE print (b)&(a-0)

[AliGW]

Welcome to the forum.

10 is NOT an Excel version - please update your forum profile.

Please provide a sample workbook - instructions in the yellow banner at the top.

[MagellanCapital]

thanks updated

[AliGW]

Maybe this:

=B2&IF(LEFT(A2,1)="0",RIGHT(A2,LEN(A2)-1),IF(VALUE(LEFT(A2,LEN(B2)))=B2,RIGHT(A2,LEN(A2)-LEN(B2)),A2))

You may need to change commas to semi-colons for your locale.

[JohnTopley]

Try

=IF(LEFT(A2,1)<>"0",IF(LEFT(A2,LEN(B2))=B2,A2,B2&A2),B2&MID(A2,2,LEN(A2)-1))

[MagellanCapital]

Maybe this:

=B2&IF(LEFT(A2,1)="0",RIGHT(A2,LEN(A2)-1),IF(VALUE(LEFT(A2,LEN(B2)))=B2,RIGHT(A2,LEN(A2)-LEN(B2)),A2))

You may need to change commas to semi-colons for your locale.

You are my hero! Thank you very much!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.