formula to extract number from alpha numeric data according to specific column criteria

[JEAN1972]

I have a worksheet where I have to extract the quantity from column H to E based on column K .

The criteria to extract to column E are U,m2,m,2U from column K , others to be disregarded

There are the word EA with the numeric DATA with trailing space in hundreds and thousand figures EA of COLUMN H (1 8000.00EA)which need to be removed if condition is met.

Looking a formula to to achieve this on a spreadsheet of 7000 rows

[JohnTopley]

Try

in E2 ....

=IF(OR(K2={"U","m2","m","2U"}),SUBSTITUTE(H2,"EA",""),"")

...leaves TEXT field ... need to revise!

[63falcondude]

In E2, try

=IF(OR(K2={"U","m2","m","2U"}),SUBSTITUTE(LEFT(H2,LEN(H2)-2)," ","")+0,"")

[JohnTopley]

...corrected



=IF(OR(K2={"U","m2","m","2U"}),SUBSTITUTE(SUBSTITUTE(H2,".00EA","")," ",""),"")

[JEAN1972]

Thank you both guys


both formula works as a charm

[63falcondude]

You're welcome. Glad we could help.

Thanks for the rep!