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 Help.

Options
  • 04-11-2014 4:13pm
    #1
    solomafioso
    Closed Accounts Posts: 1,095 ✭✭✭


    Sorry if this is in the wrong forum (please move if it is).

    I'm wondering could anyone help me with this issue?

    I have a full column which contains names followed by a six digit ID number in the one cell.

    Is there any rule I can use in the next column to export / move / remove or split it so the name is in one cell and the ID is in another?

    I tried tinkering around with conditional formatting, using =LEFT and =RIGHT formulas, but the fact that the names are of differing length throws it off.

    Thanks.


Comments

  • Options
    #2
    MatureStudent
    Registered Users Posts: 33 ✭MatureStudent


    For the name use : =LEFT(A2,(LEN(A2)-6)) which will drop the final 6 characters (make it -7 if there is a space between the name and Id)
    and for the ID use : =RIGHT(A2,6)


  • Options
    #3
    solomafioso
    Closed Accounts Posts: 1,095 ✭✭✭solomafioso


    MatureStudent wrote: »
    For the name use : =LEFT(A2,(LEN(A2)-6)) which will drop the final 6 characters (make it -7 if there is a space between the name and Id)
    and for the ID use : =RIGHT(A2,6)

    Thanks a mil! Worked like a charm.


  • Options
    #4
    Snaffles2014
    Registered Users Posts: 246 ✭✭Snaffles2014


    For the record, you can did it another way as well ... In Excel 2007 highlight the columns, you can go to the Data tab then under Data Tools, go to Text to Columns .... and use the wizard. Just play around for the best results.


  • Options
    #5
    solomafioso
    Closed Accounts Posts: 1,095 ✭✭✭solomafioso


    Snaffles2014 wrote: »
    For the record, you can did it another way as well ... In Excel 2007 highlight the columns, you can go to the Data tab then under Data Tools, go to Text to Columns .... and use the wizard. Just play around for the best results.

    Thanks for the reply. I don't think Fixed Width would work as some names are at differing lengths, and neither would Delimited as there is only a space between the name and the ID. Using that would split everything with spaces including the "Mc" in some surnames so it would output first middle surname then ID in separate cells.


  • Options
    #6
    Snaffles2014
    Registered Users Posts: 246 ✭✭Snaffles2014


    solomafioso wrote: »
    Thanks for the reply. I don't think Fixed Width would work as some names are at differing lengths, and neither would Delimited as there is only a space between the name and the ID. Using that would split everything with spaces including the "Mc" in some surnames so it would output first middle surname then ID in separate cells.

    Yeah, it mightn't be ideal, only way to find out is by giving it a go


  • Advertisement
Advertisement