Trimming text from numbers in Excel

Let's say we have a column of numbers, which includes "L" characters that you want to trim and average separate from the numbers

Column with numbers and numbers with "L", which excel treats as text. The left justify is a quick way to see that excel thinks the data is text

Since Excel treats numbers and text as text (25L is not 25, it is "25L"), averaging the column only averages the numbers and disregards the text.

Text disregarded by average function

Here is the full formula to strip the "L" off the end of the numbers

=IF(ISTEXT(B1),NUMBERVALUE(LEFT(B1,FIND("L",B1)-1)),"")

Let's break this down:

Check for Text, i.e. not a number:

=IF(ISTEXT(B1)

If it is, grab the number value (force the text output to a number)

,NUMBERVALUE(

And then take all the characters left of the "L" (index L - 1) or return blank

LEFT(B1,FIND("L",B1)-1)),"")

and then you can get a column of numbers with "L"s stripped

Since this new column contains numbers, it can be averaged and all numeric formulas should work with it.

Instead of using text with numbers it would be much easier to implement a drop down menu by making a list:

Data validation within the Data Tools section on the Data Ribbon tab

Allow only the list and choose the source as the cells you have your list in. I would put the list either in hidden columns way off to the right of your work area, or on a separate hidden worksheet to not cause confusion.

After you have the list setup, averaging is as simple as =AVERAGEIFS(B1:B9,A1:A9,"Apple") while replacing "Apple" with whatever your text string is.