Text extract formula in Excel
Hi guys!
The question is this:
I have fiew strings that i have to
extract text from.
JOHNNIE WALKER BLACK.CY
JOHNNIE WALKER BLACK 200ML/FL.CY
and
JOHNNIE WALKER BLACK 1.75L.CY
CY means - Current Year
For the first two I have a formula
which works fine:
=LEFT(O9,FIND(".",O9)-1)
O9 is where the string is.
It returns just the name without CY,
exactly what I need.
But on the 3 one it wouldn't work
because it has 2 periods.
I need a universal formula for any
size of the string, which will extract
me just the name. CY is constant on
every entry.
Appriciated, Misha1
The question is this:
I have fiew strings that i have to
extract text from.
JOHNNIE WALKER BLACK.CY
JOHNNIE WALKER BLACK 200ML/FL.CY
and
JOHNNIE WALKER BLACK 1.75L.CY
CY means - Current Year
For the first two I have a formula
which works fine:
=LEFT(O9,FIND(".",O9)-1)
O9 is where the string is.
It returns just the name without CY,
exactly what I need.
But on the 3 one it wouldn't work
because it has 2 periods.
I need a universal formula for any
size of the string, which will extract
me just the name. CY is constant on
every entry.
Appriciated, Misha1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi misha1
If you need to return all you text accept .CY then this formula will work universally.
=REPLACE(O9,FIND(".CY",O9)
It is case sensitive though.
Antrat
If you need to return all you text accept .CY then this formula will work universally.
=REPLACE(O9,FIND(".CY",O9)
It is case sensitive though.
Antrat
Or you could use your old formule and just replace the "." look-up string by a refined string ".CY"
Geert
Geert
=LEFT(09,LEN(O9)-3)