Change phone number in bulk
Hi all. I need help in replacing the 5th number to 2 in all the numbers in bulk. I have 5000 numbers to replace to a single digit. Please help me sort this out. Please see the sample below. Thanks.
03214934338
03216232859
03214791496
03212923342
03213192614
03211901833
03214557002
03215201951
03216486010
03213476555
03211331124
03212499960
03216085452
03216150389
03218023189
03215573889
03213265385
03214650373
Welcome to the forum, Nj.
If your data is in A1:A5000, then in B1 put the formula:
=REPLACE(A1,5,1,2)
Fill that down to B5000 (or your last row of data). Select B1:B5000 and choose Copy, then right-click that range and choose PasteSpecial > Values. You can then delete A1:A5000 if you no longer need the original numbers.
Your example numbers all begin with 0321 and you want the next digit to be a 2. Is that always the case for your numbers? If so, you can use Find & Replace to change them in situ. First of all, ensure that the column is formatted as TEXT, otherwise you will lose the leading zeros. Then CTRL-H, and:
Find what: 0321?
Replace with: 03212
Click Replace All.
Hope this helps.
Pete
Or this:
=LEFT(A1,4)&"2"&RIGHT(A1,6)
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Dear Paul
Thanks for your reply. I got it.. Its amazing. It solved my every problem.
=REPLACE formula is awesome.
Thanks
Javed
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks