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 Sorting phone numbers and adding prefixes

Options
  • 02-06-2009 7:10pm
    #1
    DanGerMus
    Registered Users Posts: 1,525 ✭✭✭


    Hey guys, i've basically got a list of numbers that i have to send a few messages to over the next few days, about 1600 in all.

    My problem is that i need all the list to be in the format 35386xxxxxxx but alot are either 08xxxxxxx or just 8xxxxxxx.

    Is there a way i can do it quickly without goin through one by one.
    I'm not a progammer obviously but i figure you guys ill know what to do.


Comments

  • Options
    #2
    hullaballoo
    Administrators, Entertainment Moderators, Social & Fun Moderators, Society & Culture Moderators Posts: 18,727 Admin ✭✭✭✭✭hullaballoo


    First step:

    I'm assuming the numbers are arranged starting from A1 in sheet1 and continuing down to ~A1600.

    Paste the following into B1: =IF(LEFT(A1,1)="8","0"&RIGHT(A1,9),RIGHT(A1,10)).

    Double-click the little tag on the bottom right-hand corner of the cell. This should copy the formula down to A1600 or whatever it is.



    Second step:

    Select a sheet2.

    Select column A and go format>cells and select 'Text' from the list.



    Third step:

    Copy the list from column B of the original sheet1 that should now have all '08' prefixes.

    Go Edit>Paste Special and select 'Values'.


    Fourth step:

    Insert the following code into cell B1 of sheet2: =IF(LEFT(A1,1)="0","353"&RIGHT(A1,9))

    Double-click the tag on the bottom right-hand side of the cell.

    Job done.



    If that sounds like a lot of work, send it to me, I'm bored and if there are any bugs I can fix them.


Advertisement