Removing ea from quantities listed as 1ea or 25ea or 500ea

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with an imported text file that lists sales quantities such as

1e
23e
500e
and etc

I would like to calculate average price using these quanities but need to remove the "ea" first

I tried the LEFT and RIGHT functions, but how do I work with the fact that the quantities are not all the same number of digits long (i.e. - 1ea vs 25ea)

I know there is a solution, but it escapes me thus far.

Many thank

Chris H (Seattle)
 
Hi
one way: in the adjacent column/cell enter the formula
=--LEFT(A1,LEN(A1)-2)
the '--' converts the text to a number
copy this down
 
Why not use find & replace function

Highlight column
find = ea
replace = nothing

this will remove ea leaving the numbe
 
Hi Chris,

=VALUE(LEFT(A1,LEN(A1)-2))

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

Chris H said:
I am working with an imported text file that lists sales quantities such as:

1ea
23ea
500ea
and etc.

I would like to calculate average price using these quanities but need to remove the "ea" first.

I tried the LEFT and RIGHT functions, but how do I work with the fact that
Click to expand...
the quantities are not all the same number of digits long (i.e. - 1ea vs
25ea).
 
There are probably other ways, but I use the <Replace> function. For your example put ea in the find field aand leave the replace empty/blank. Caution: You need to mark which cells you want this function to work on, but remember it will take out ALL ea, including cells that have things like 'Teacher' (becomes 'Tcher')
----- Chris H wrote: ----

I am working with an imported text file that lists sales quantities such as

1e
23e
500e
and etc

I would like to calculate average price using these quanities but need to remove the "ea" first

I tried the LEFT and RIGHT functions, but how do I work with the fact that the quantities are not all the same number of digits long (i.e. - 1ea vs 25ea)

I know there is a solution, but it escapes me thus far.

Many thank

Chris H (Seattle)
 
Back
Top