Hi, I have addresses of customers to correct from Mumbai-42 or Mumbai-93 i.e. Mumbai-xx to be changed to Mumbai 400042 or Mumbai 400093 i.e. Mumbai 4000xx
Please note that some pincodes are correctly given in the addresses. So existing 6 digit pin codes should not get altered.
Is there a formula to apply to this correction from 2 digit with 6 digists with a standard suffix 4000 if the preceding city name is Mumbai followed by a dash and a 2 digit number
Thanks for your help
Try
=SUBSTITUTE(SUBSTITUTE(A1,"-"," 4000"),40004000,4000)
Last edited by Ace_XL; 12-27-2015 at 07:09 AM.
Life's a spreadsheet, Excel!
Say thanks, Click *
Hi Ace_XL, Thanks. Your formula works if it is Mumbai-42 and makes it Mumbai 400042. But if the already correct codes such as Mumbai-400042 is becoming Mumbai 4000400042
Can we restrict the formula to only where 2 digits are present after Mumbai-.
Hope I have been able to explain clearly
Try just the inside substitute by itself maybe?
=SUBSTITUTE(A1,"-"," 4000")
That just replaces the - with a space and 4000
Edit:
If there are codes that do have a "-" then maybe:
=IF(LEFT(A1,6)="Mumbai",SUBSTITUTE(A1,"-"," 4000"),A1)
Last edited by Beamernsw; 12-27-2015 at 07:24 AM.
Perfect!! Thanks, Ace_XL.
This is great!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks