HELP!!!!!!Urgent
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: HELP!!!!!!Urgent

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    ok, lets say your list starts in cell A2 and goes down, I will give you three formula for cells B2,C2, & D2, that you need to enter then fill down. I am assuming that the format is always the same, as in your list, and always has all components shown, including the terminating full-stop.

    cell B2...
    =LEFT(A2,FIND(",",A2)-1)

    cell C2...
    =MID(A2,FIND(",",A2)+2,LEN(A2)-(FIND(",",A2)+4))

    cell D2...
    =LEFT(RIGHT(A2,2),1)




    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    PS: as will anything like this, i recommend you copy the resulting lists to VALUES ONLY, to get rid of the equations once the job is done.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    (hey this topic clean disappeared! I am adding new reply to it in order to try to get it to reappear)

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Newcastle, UK
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-14 11:04, sugar1525 wrote:
    I have a column in EXCEL spreadsheet where the lastname, firstname n the middle initial are in the same column. Something like this..
    Abdul, Wes A.
    Able, Ursula B.
    Albert, Tuome C.
    Alexi, Toni D.
    Al-Sabah, Tommie E.
    Alstain, Tom F.
    Aruda, Theo G.

    Now I have to separate these first, last and middle initials using functions in 3 seperate columns. I dont want the comma and period when these are displayed in separate columns. I know I can do it by going to Data and then Text to column. But I want to do it by using functions like left, right and mid. Can Somebody help me...It's urgent

    Thanx
    if it's at all likely that some people may not have a middle intial, use.

    Name in A1.

    B1 =LEFT(A1,FIND(",",A1)-1)
    C1 =IF(ISERROR(FIND(" ",RIGHT(A1,FIND(",",A1)-2))),RIGHT(A1,FIND(",",A1)-2),MID(A1,FIND(",",A1)+2,FIND(" ",RIGHT(A1,FIND(",",A1)))))
    D1 =IF(ISERROR(MID(A2,FIND(".",A2)-1,1)),"",MID(A2,FIND(".",A2)-1,1))

    hope this helps!
    "Have a good time......all the time"
    Ian Mac

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx so much guys..

    It works fine..

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a column in EXCEL spreadsheet where the lastname, firstname n the middle initial are in the same column. Something like this..
    Abdul, Wes A.
    Able, Ursula B.
    Albert, Tuome C.
    Alexi, Toni D.
    Al-Sabah, Tommie E.
    Alstain, Tom F.
    Aruda, Theo G.

    Now I have to separate these first, last and middle initials using functions in 3 seperate columns. I dont want the comma and period when these are displayed in separate columns. I know I can do it by going to Data and then Text to column. But I want to do it by using functions like left, right and mid. Can Somebody help me...It's urgent

    Thanx

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    I was wondering if I can use one function to get the first name for all the values and similarly for lastname and middle initial coz I have around 114 records and it will be tedious to write the function for the 114 values

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Once you have written the equation against one row, grab the fill button (the little square on the bottom right of a selected cell) and drag it down to fill the equation through the list. Alternatively, double click the fill button to automatically fill the equations down to the bottom of the list.

    The cell references change for you, when you do this.

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    How bout the Data Text to Columns comand?


    Denny

    [ This Message was edited by: kinkyparamour on 2002-04-15 09:39 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com