Trimming alphanumeric column that is not standard lenght

[iali]

Hello All,

I have a data set in a column. The data set is a combination of alphabet and numeric.
1. Data always starts with MG followed by numbers and then may end with a letter. e.g MG1234, MG1234A, MG12345B
2. Length of the string is not always the same. e.g MG1234, MG12345, MG1234A, MG12345B
3. the Desired outcome is that for each one of these entries translates to remove the last alphabet and populate a column beside it. e.g MG1234=>MG1234, MG1234A=>MG1234, MG12345B=>MG12345.

Raw data Desired outcome
MG16828 MG16828
MG1761 MG1761
MG182303 MG182303
MG9161C MG9161
MG1234B MG1234
MG13434A MG13434

[Rick Rothstein]

Assuming your data starts in cell A1, give this formula a try...

=LEFT(A1,LEN(A1)-(RIGHT(A1)>"9"))

[iali]

Assuming your data starts in cell A1, give this formula a try...

=LEFT(A1,LEN(A1)-(RIGHT(A1)>"9"))

Thank you Rick, this works great. How would we modify if the last character is not a alphabet but a symbol like an * eg MG12345*

[JohnTopley]

Please try

=LEFT(A1,LEN(A1)-(OR(CODE(RIGHT(A1))<48,CODE(RIGHT(A1))>57))*1)

[Rick Rothstein]

Give this formula a try (it returns the empty text string "" for a blank cell rather that an Error code)...

=LEFT(A1,LEN(A1)-(ABS(CODE(RIGHT(0&A1))-52.5)>4.5))

[Phuocam]

Or try:

=LEFT(A1,LEN(A1)-ISERR(--RIGHT(A1)))