Which formula do you need?
Each formula assumes the number is in cell A2. Type it in the next empty column and drag it down.
| Task | Formula |
|---|---|
| Add 91 to a clean 10-digit number | ="91"&A2 |
| Add +91 | ="+91"&A2 |
| Remove 91 from a clean 12-digit number | =RIGHT(A2,10) |
How do you clean a column of mixed phone formats?
Real lists are messier than that table. One person can turn up five ways after a year of walk-ins, form fills and old exports, and Excel sees five different people.
=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2," ",""),"-",""),"+",""),10)The formula strips spaces, dashes and the plus sign, then keeps the last 10 digits. We ran it against each of these inputs before publishing.
| In the sheet | After the formula |
|---|---|
| 9876543210 | 9876543210 |
| 098765 43210 | 9876543210 |
| +91 98765-43210 | 9876543210 |
| 91 9876543210 | 9876543210 |
| 919876543210, stored as a number | 9876543210 |
How do you find duplicate phone numbers?
- Clean the numbers into column B with the formula above.
- Check each one with
=AND(LEN(B2)=10,ISNUMBER(--B2),--LEFT(B2,1)>=6)in column C. FALSE means it isn't a valid Indian mobile number. - Count repeats with
=COUNTIF(B:B,B2)in column D. Anything above 1 is the same person again. - Select column B, copy it, then use Paste Special and choose Values, so the numbers stop depending on the formula.
- Select your table, open the Data tab, click Remove Duplicates and tick only column B.
Excel keeps the first row it finds and deletes the rest. If the later row has a better name or an email, sort your sheet first so the row you want comes out on top.
What goes wrong with these formulas?
Numbers from other countries are the dangerous case. A UK number like +44 7911 123456 comes out as 7911123456, which passes the check and looks Indian. Filter those rows out before you clean anything.
A 12-digit number sometimes shows as 9.19877E+11. That's only the display, and the value underneath is intact. Widen the column, or format it as Number with 0 decimal places.
If a formula sits in the cell as plain text and won't calculate, that column is formatted as Text. Switch it to General and enter the formula again.
Common questions
Can I remove +91 with Find and Replace?
Yes. Press Ctrl+H, find +91 and replace it with nothing. Never search for 91 alone, because Excel will also cut those digits out of the middle of numbers like 9891234567.
Can I add 91 with a custom number format?
A format such as "91"0000000000 only changes what you see. The stored number stays at 10 digits, so an export or upload won't include the 91.
Do these work in Google Sheets?
Yes. RIGHT, SUBSTITUTE, COUNTIF and the rest use the same syntax there. Remove Duplicates lives under Data, then Data cleanup.
How do you stop cleaning the same list every month?
This job comes back whenever a new export lands. Miaalo does the same cleanup on every upload and form entry. It reduces each number to 91 plus 10 digits, skips the invalid ones, and adds repeat people to the new source with no second record. Start with our free CRM Excel template, which uploads as it is.