Formula to Remove Last Alpha Characters

[legalhustler]

I have the following 8A, 20C, 110J. I want a formula to remove the letters from the far right, so that it returns 8, 20, 110. Letters can only be from A-Z. I think it's a pretty simple formula but my brain is making me think of something more complicated.

Would like to avoid using any helper columns.

 

[Special-K99]

If there is only one letter and it's always on the right then just use

=LEFT(A1,LEN(A1)-1)

otherwise supply data that doesn't conform to that pattern

 

[AlanY]

try this

=IF(AND(CODE(RIGHT(A1,1))>=65,CODE(RIGHT(A1,1))<=90),LEFT(A1,LEN(A1)-1),A1)

 

[legalhustler]

try this

=IF(AND(CODE(RIGHT(A1,1))>=65,CODE(RIGHT(A1,1))<=90),LEFT(A1,LEN(A1)-1),A1)

Bingo! This is more robust and accounts whether I have a letter or not.

Thanks all.

 

[duhfus49]

I tried this one and the one below with no luck. Below is what I have and I am trying to delete the L and keep the decimal point.

	Local		USGA	Trend
Riverview	Number	Player Name	Index	Index
Riverview	115122	AARNESS, VERLE	24.3	25.3L
North Golf Course	115201	ABEEL, MYRON	12.4	12.4L
Lakes West	118923	ABLEIDINGER, LEON	24.2	24.1L
Lakes West	118269	ABY, BILL	29.0	29.0L
North Golf Course	133798	Adams, Sam	n/a	18.4L
Willowcreek	39290	ADAMS, TERRY	16.3	16.3L
Lakes West	102008	ADAMS, TOM	15.9	16.5L
Willowcreek	127484	ADLER, MARCUS	15.2	15.2L
Willowcreek	146115	ADLER, MARK	19.7	19.7L

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Special-K99]

It's impossible to tell how many characters you have in the last column.
When I copied and pasted to a new spreadsheet it added a space at the end (this could be the fault of the forum or you may indeed have "L " at the end of each value in that column.

Anyway this works after copying and pasting your data

=LEFT(E2,LEN(E2)-2)+0

If this doesnt work for you then you need to paste a spreadsheet version

FYI

You cant attach files on this forum. Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[duhfus49]

Here is the link to my file.

https://drive.google.com/file/d/1E08JYifTx6Pqhl9sMYn9KYsZOpkQlqFd/view?usp=sharing

 

[Special-K99]

This works

=IF(LEFT(E3,3)="n/a","n/a",LEFT(E3,LEN(E3)-3)+0)

 

[Scott Huish]

Based on your sheet, it looks like you really only have two scenarios, it either ends in L or is n/a, so is easier than the original question.

=IFERROR(SUBSTITUTE(E3,"L","")+0,"n/a")

 

[duhfus49]

Thank you so much. It works great. Have a most wonderful day today.