Remove only characters from cells from last

Hello Team,

Any excel formula which will Remove only characters from cells from last

Example1

Input - TEN5215ECX
Output - TEN5215

Suffix can have any lenght alphabets if found it should be removed after last Integer value
Sometimes it only have integer values so there should be no action

Example2

Input - T 5215
Output - T 5215

Example3

Input - 5215
Output - 5215

Any Excel formula for this, please assist

Hi Parth,

Here is a User Defined Function (UDF) that will do what you want.
PS: I didn't know this was a team event.

Please Login or Register  to view this content.

Here is a formula solution

in B1 and copy down

=LEFT(A1,LOOKUP(1,-MID(A1,ROW(A$1:A$99),1),ROW(A$1:A$99)))

Data Range

	A	B
1	TEN5215ECX	TEN5215
2	T 5215	T 5215

Thanks Marvin.. Its of a great help.. I have used it in a Macro file whle such occurances occur

Alkey - Thanks to you too.. as the excel formula was also required in some reports where Functions can not be implemeneted..(Its worked like charm)

One doubt.. it worked well...
But gave #N/A for below Example.. sorry i missed to specify this example

Example4

Input - Team
Output - Team

here it should not do anything except keep "Team" as it is..
puzzled if it is possible

OK Parth,

Here is a little extra in the UDF to take care of the strings without numbers in them.

Please Login or Register  to view this content.

No problem

Try it now

=IFERROR(LEFT(A1,LOOKUP(1,-MID(A1,ROW(A$1:A$99),1),ROW(A$1:A$99))),A1)


Data Range

	A	B
1	TEN5215ECX	TEN5215
2	T 5215	T 5215
3	Team	Team

Grt Thanks Marvin & Alkey.... simply superb