Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete alpha characters from cell (2003)

    I wonder if someone could help me with this task: I have cells that contain, for example, Smith, John. I would like the cell to contain just the last name only; no apostrophe or first name. Is there a way to do this via a formula?

    Thanks in advance!
    Deb

  2. #2
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete alpha characters from cell (2003)

    Hans,
    you means to say this? =LEFT(A2,FIND(",",A2)-1)

    Deb, pls note that this formula will return all characters on the left side of a ,
    in this case Smith is returned based on your example. If your full name include other characters beside
    a , as separator, this will not work properly

    If the last name is the other way round, try this
    =RIGHT(A2,LEN(A2)-FIND(",",A2)) which will return all characters after a , eg Smith in John, Smith

    HTH

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

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

    Re: Delete alpha characters from cell (2003)

    Thank you, I did mean =LEFT(A2,FIND(",",A2)-1)

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

    Re: Delete alpha characters from cell (2003)

    Edited by HansV to correct error in formula (thanks to Franciz for pointing it out!)

    You can use a formula to extract the last name to another cell, for example the cell next to the one with the full name.
    If you have a full name in cell A2, the following formula in B2 will extract the last name:
    <code>
    =LEFT(A2,FIND(",",A2)-1)
    </code>
    You can fill this formula down.
    If you wish, you can copy the range with formulas, paste special as values, then delete the original data.

    If you want to replace the names within the cells themselves, without using intermediate formulas, you'd have to use VBA code.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete alpha characters from cell (2003)

    Thank you Hans and Franciz. I delayed in posting back as I was trying to get the original formula to work (I didn't see Franciz' post until just now!). The benefit of the tiny error in the original formula is that it forced me to do a bit reading about this formula which led to a better understanding of how it works!

    All is well and thank you both for the instruction!

Posting Permissions

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