::: Telephone Number Comparison or Data Integrity

[infojacques]

Hi, I have an Excel file full of telephone numbers that are like
this: +001 (812) 123 123 123 Into a new tab I will change them to:
+001812123123123 Into a 3rd tab I would like to ensure that I only
deleted the spaces and the parenthesis (data integrity). I am
looking for a formula that adds each number within the cell and
compare these 2 totals. In other words:

Tab1
+001 (812) 123 123 123
0+0+1+8+1+2+1+2+3+1+2+3+1+2+3=30

Tab2
+001812123123123
0+0+1+8+1+2+1+2+3+1+2+3+1+2+3=30

Tab3
30 compare to 30 = ok if not = bad

Would anyone know how to do this? Or have a better suggestion?
Many thanks for your help. Jacques

 

[Gord Dibben]

First you should delete spaces and parens.

Note: you will lose the leading zeros in your example data.

Format the cell to show them................000 up to 15

Then use this formula in a helper cell to get the sum of digits.

=SUMPRODUCT(--MID($A$1,ROW(INDIRECT("1:" & LEN($A$1))),1))


Gord Dibben MS Excel MVP

 

[infojacques]

Hello Gord, Thank you for your solution. I will try to apply
it and understand it. But this will be tomorrow. I wish you an
excellent week-end. Jacques