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.

[TABLE="width: 537"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Local[/TD]
[TD] [/TD]
[TD]USGA[/TD]
[TD]Trend[/TD]
[/TR]
[TR]
[TD]Riverview[/TD]
[TD]Number[/TD]
[TD]Player Name[/TD]
[TD]Index[/TD]
[TD]Index[/TD]
[/TR]
[TR]
[TD]Riverview[/TD]
[TD]115122[/TD]
[TD]AARNESS, VERLE[/TD]
[TD]24.3 [/TD]
[TD]25.3L [/TD]
[/TR]
[TR]
[TD]North Golf Course[/TD]
[TD]115201[/TD]
[TD]ABEEL, MYRON[/TD]
[TD]12.4 [/TD]
[TD]12.4L [/TD]
[/TR]
[TR]
[TD]Lakes West[/TD]
[TD]118923[/TD]
[TD]ABLEIDINGER, LEON[/TD]
[TD]24.2 [/TD]
[TD]24.1L [/TD]
[/TR]
[TR]
[TD]Lakes West[/TD]
[TD]118269[/TD]
[TD]ABY, BILL[/TD]
[TD]29.0 [/TD]
[TD]29.0L [/TD]
[/TR]
[TR]
[TD]North Golf Course[/TD]
[TD]133798[/TD]
[TD]Adams, Sam[/TD]
[TD]n/a [/TD]
[TD]18.4L [/TD]
[/TR]
[TR]
[TD]Willowcreek[/TD]
[TD]39290[/TD]
[TD]ADAMS, TERRY[/TD]
[TD]16.3 [/TD]
[TD]16.3L [/TD]
[/TR]
[TR]
[TD]Lakes West[/TD]
[TD]102008[/TD]
[TD]ADAMS, TOM[/TD]
[TD]15.9 [/TD]
[TD]16.5L [/TD]
[/TR]
[TR]
[TD]Willowcreek[/TD]
[TD]127484[/TD]
[TD]ADLER, MARCUS[/TD]
[TD]15.2 [/TD]
[TD]15.2L [/TD]
[/TR]
[TR]
[TD]Willowcreek[/TD]
[TD]146115[/TD]
[TD]ADLER, MARK[/TD]
[TD]19.7 [/TD]
[TD]19.7L [/TD]
[/TR]
</tbody>[/TABLE]

 

[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.