Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Excel Advanced Search?

Options
  • 28-05-2003 4:09pm
    #1
    ballooba
    Registered Users 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

  • Options
    #2
    Capt'n Midnight
    Moderators, Recreation & Hobbies Moderators, Science, Health & Environment Moderators, Technology & Internet Moderators Posts: 91,761 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


  • Options
    #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