Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of MID function (XP)

    Have a list of names in column B set up like lastname, firstname. I have used =MID(B2, Find(" ",B2+1,25) to copy the firstname to column C. Is there a method that will do the same thing, plus delete the comma firstname from column B? Basically it is move the first name from B to C, and delete the firstname and the comma separator from B.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of MID function (XP)

    You cannot use a formula to change the value of the cell containing the formula - it would cause a circular reference.
    You could set up another formula in column D to extract the last name, then select columns C and D, copy, paste special > values to replace the formulas with values.
    You can then safely delete column B.

    Alternatively, you could write a macro to fill column C with the first names and column B with the last names.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of MID function (XP)

    Thanks, Hans. Yes I know about the splitting to separate columns and paste special. Just looking for a method to eleminate steps. Dawned on me that I used Text to Columns in order to do what I wanted. Just had to find an old spreadsheet where I had done it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of MID function (XP)

    Yep, Text to Columns is the easiest method. There's just a tiny problem with that: if you tick just comma as delimiter, Excel will include the space before the first name in the second column. If you tick both comma and space as delimiters, you'll get rid of that space, but Excel will split double first or last names into different columns.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use of MID function (XP)

    If you use Text to Columns, you could split it at the comma, and then you would have to use a TRIM() formula to remove the leading space from the Surname. A possible remedy to avoid having to use any functions is to run Find (" ") and replace with ("") first to eliminate the space, then run Text to Columns and separate with a comma delimiter.
    Regards,
    Rudi

  6. #6
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of MID function (XP)

    Thanks for the tip about comma as delimiter. I just used the space as a delimiter. As for the double last name, there were only a couple so it wasn't difficult to fix. Is there a method to keep hyphenated names from not splitting?

  7. #7
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of MID function (XP)

    Thanks for the tip.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of MID function (XP)

    If you also want to split hyphenated names, tick the Other check box and enter a hyphen in the box next to it.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Use of MID function (XP)

    A hyphenated name should not split if you use Text to Columns with a space delimiter; unless you have the name typed as : Jenny Smith - Moore. If this is the case, run another Find and Replace, and search for ( - ) and replace with (-). This results in : Jenny Smith-Moore, which will not be split by the Text to Columns.
    Regards,
    Rudi

Posting Permissions

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