Strip numbers from columns

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

Karniem,

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

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote

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

Reply | Quote