Remove alpha character in alphanumeric string

**fun4four** · 07-28-2010, 01:41 PM

Does anyone have an easy way to remove alpha characters in a alph/numeric string. I deal a lot with "bills of material". They concist of reference designators in the from below:

C1,C3,C6,C27,C40 or
R2,R6,R70,R120 or
CR2,CR5,CR7,CR40 or as well as different alpha characters. The only thing the strings have in common is that they have the same alpha character before the number as well as a comma seperating them. What im looking for is the following:

C1,C3,C6,C27,C40 to be C1,3,6,27 or
R2,R6,R70,R120 to be R2,6,70,120 etc.

Is there an easy way using formulas or macros to accomplish this. Thanks in advance for the help. Bob

**DonkeyOte** · 07-28-2010, 02:36 PM

If we assume A1:A3 hold strings:

C1,C3,C6,C27,C40
R2,R6,R70,R120
CR2,CR5,CR7,CR40

Then

Please Login or Register  to view this content.

should return

C1,3,6,27,40
R2,6,70,120
CR2,5,7,40

in B1:B3

there are other methods available (eg UDF - RegExp etc...) but the above is pretty simple approach I think.

**fun4four** · 07-28-2010, 03:46 PM

Yes again, you are the "Excel Man", I assume.....

I came up with this during lunch but only because I had some other info avaliable to me in my sheet,
=CONCATENATE(LEFT(E2,1),SUBSTITUTE(G2,LEFT(G2,1),"")) where E2,1 ="C" and G2,1 represents the alpha character i want to get rid of. Yours works better because it does not leave characters on blank lines when copied down, whereas mine does. Thank you again.

I do have a real tuff one for you though. If I could get a formula, macro or something else to do this for me that would be the "topping on the cake". If at all possible if you could email me at I could send you my file with an explanation of what I am trying to accomplish. Any help in that arena would by greatly appreciated and make my job more easy. Thank you again. Bob

**DonkeyOte** · 07-28-2010, 04:22 PM

If you have another (unrelated) question by all means create a new thread on the board - you can attach a sample file.

There are plenty of members around here who will be happy to help you out as best they can.

(I would advise you remove your email address from your prior post else you are at risk of being spammed to oblivion)