Using a vlookup return cell with mixed elements in a subsequent calculation

**jonesyhouse** · 12-09-2012, 07:36 PM

Hi, I have successfully returned "EUR 0.65 " in a cell from a vlookup return.

The problem is that i need to multiply the returned value (0.65) by a another number say 1,000 but I keep getting an error as I guess I somehow need to strip out or deal with the text element of EUR in the cell.

Any help greatly appreciated. Thanks in advance

**mrice** · 12-09-2012, 07:43 PM

Try

=VALUE(RIGHT(A1,LEN(A1)-4))

to extract out the numeric part.

**Moo the Dog** · 12-09-2012, 07:46 PM

If your amounts change, for instance 0.65 one time and 12.72 another time, then try this (assuming your value is in A1, and there will always be a space between EUR and the amount):

=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1)+1))

- Moo

**FDibbins** · 12-09-2012, 07:48 PM

if the answer the vlookup is giving you is "EUR 0.65" then that is text, not value - you cant do calcs on text, you need to convert it. try something like

=VALUE(MID(A1,SEARCH(" ",A1,1)+1,6))

adjust the references as needed

**jonesyhouse** · 12-10-2012, 07:02 AM

Excellent many thanks to all of you . Indeed the solutions offered have worked perfectly for me.

**twiggywales** · 12-10-2012, 07:45 AM

if you are using the mid function to get a number and then using that number in further calculations (0.65 *1000) etc then I don't think you need to use value as excel will automatically read 0.65 as a number once its used in a mathmatical equation

(same logic behind not using the -- in this
=sumproduct((a1:a10=1)*(b1:b10=2))