Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts
    We have a user who has two columns of user IDs which consist of letters followed by numbers (e.g., ABC1234, DE5678, JFK3346, IG4435, etc.). There are always four numbers at the end, but there may be two or three initials at the beginning, and there are no spaces in the IDs to serve as delimiters. He would like to be able to "replace" all the numbers in each column with nothing (strip the numbers), leaving him with just two columns of initials.

    Is there a way to accomplish that in Excel? A formula? A macro? A split columns to text function? Something?

    We know we can export the data to Word, run a find and replace, and then copy it back to Excel, but is there any way to do it completly in Excel?

    Many thanks,

    Karnie
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

  2. #2
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by karniem View Post
    We have a user who has two columns of user IDs which consist of letters followed by numbers (e.g., ABC1234, DE5678, JFK3346, IG4435, etc.). There are always four numbers at the end, but there may be two or three initials at the beginning, and there are no spaces in the IDs to serve as delimiters. He would like to be able to "replace" all the numbers in each column with nothing (strip the numbers), leaving him with just two columns of initials.

    Is there a way to accomplish that in Excel? A formula? A macro? A split columns to text function? Something?

    We know we can export the data to Word, run a find and replace, and then copy it back to Excel, but is there any way to do it completly in Excel?

    Many thanks,

    Karnie
    If there are always 4 digits at the end, he could use the following formula

    =LEFT(A1,LEN(A1)-4)

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Karniem,

    You might find =Right(a1,4) easier.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by RetiredGeek View Post
    You might find =Right(a1,4) easier.
    Won't this return just the numbers? I thought the question was to remove the numbers, and return just the text.
    Regards
    John



  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    John,

    You're right...I should have read the question more carefully.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Minnesota, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A big thank you to Gfamily The formula =LEFT(A1,LEN(A1)-4) works perfectly as all of our user IDs have the four digits at the end.

    We, too, had thought of =Right(a1,4) and similar things, but they, of course gave us just the numbers.

    Thank you all for your responses. I've used this lounge for several years now, and have never failed to get help when needed. You are all much appreciated!

    Karnie
    Karnie Moesenthin
    Technical Training and Development Professional
    Legal Industry

Posting Permissions

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