Remove last character of a string if it's A-Z

[dburge07]

Is it possible to search a column, and remove the last character(s) if they are not numerical?

For example:

if column A had values R065O, Sub10, Sub11, Sub12,Sub13,Sub14,Sub15,Sub16,Sub17,Sub18,Sub19,Sub1C,Sub20,Sub21,Sub23,Sub24,Sub25,Sub2C
would like to return: R065, Sub10, Sub11, Sub12, Sub13, Sub14, Sub15, Sub16, Sub17, Sub18, Sub19, Sub1, Sub20, Sub21, Sub23, Sub24, Sub25, Sub2


I would like to remove the letters from the END of the contents. I do not want to remove anything to the LEFT of the numbers, only the RIGHT.

Thanks in advance!

Sample book attached.

[queuesef]

if your data is in column A, copy the following formula (if not adjust ALL occurances of cell A1 to your cell)

Please Login or Register  to view this content.

[JieJenn]

In B2, enter =IF(ISNUMBER(INT(RIGHT(A2,1))), A2,LEFT(A2, LEN(A2)-1)) then autofill the formula.

[dburge07]

I understand I can accomplish with that formula.. but I really need it to be embedded in my VBA code and execute after I create the sorted sheet.
I'm hoping to automate this.. IE loop through columns A,E,I,M, and Q respectively.. and trim any letters off the end of the numbers.

I am trying to Eliminate anything after the number designation so I can create relationships in my report software utilizing the tag values.

Essentially I am taking a complete value , inserting a row & breaking out the first 6 characters using =LEFT... then I want to loop through the created columns and remove any letters after the numerical portion of the tag.

See attached samplebook...

[jindon]

VBA

Please Login or Register  to view this content.

[dburge07]

This is perfect.

all other solutions appreciated and rep given but this one is definitely the approach I'm using! Thanks!!!

[Rick Rothstein]

Just noting that if you put this array-entered** formula in cell I2 instead of the one you have there now and copy it down, you would not need to do what you asked for in this question as the entry would be correct directly from the formula...

=LEFT(J2,MAX(IF(ISNUMBER(0+MID(J2,ROW(I$1:I$99),1)),ROW(I$1:I$99))))

**Commit this formula using CTRL+SHIFT+ENTER and not Enter by itself