Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Excel Advanced Search?

  • 28-05-2003 04:09PM
    #1
    ballooba
    Registered Users, Registered Users 2 Posts: 6,315 ✭✭✭


    My head is wrecked. i have an excel spreadsheet with 3000 phone umbers and names in it.

    The data is in the format::
    FName:LName:Mobile

    Some of the mobile numbers are in the format:
    086XXXXXXX

    I want all the mobile numbers in the format:
    35386XXXXXXX

    When I run an excel replace:
    086 -> 35386

    It also changes the instances where it finds 086 in the number, is there any way of doing it just on the data at the start of the cell.

    We will put it into an access database next year but I might have to do it now.


Comments

  • #2
    Capt'n Midnight
    Moderators, Recreation & Hobbies Moderators, Science, Health & Environment Moderators, Technology & Internet Moderators Posts: 96,008 Mod ✭✭✭✭Capt'n Midnight


    Is there a space or dash or comma after the area code
    eg: "086 " or "086-" - in which case you could change it that way



    Make a new column to generate the numbers
    =IF( (left(a1,3)=353), a1 , "353"&a1 ) or something like that

    BTW +353 is more usual (+ meaning dial 00 or the local equilivant)
    or better yet +353,xxx,xxxxxxx


  • #3
    dahamsta
    Banned (with Prison Access) Posts: 16,659 ✭✭✭✭dahamsta


    Highlight the telephone number column and copy and paste it into EditPlus. Hit CTRL+H in EditPlus and type ^086 in the Find field. Type 35386 in the Replace field, and check the Regular Expression box on the left. Hit Replace All. Now copy and paste the lot back into Excel.

    adam


Advertisement