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

2. Originally Posted by karniem
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. Karniem,

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

RG

4. Originally Posted by RetiredGeek
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.

5. John,

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

RG

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

Posting Permissions

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