lookup and change value in 1 formula

Hi guys.

Have an interesting challenge.

I am gathering information on pricing from my suppliers into a spreadsheet.
One of my suppliers delivers a pricelist where the values in the column price are like: "4,90€/kg"
In my spreadsheet I need these values, but I need them to be numeric so I can calculate sales prices from them.

I can get them in by using a simple vlookup... but then all text characters need to be removed from the values, preferrably in the same formula, so once its done it will keep working on new input.
Any ideas?

A solution for this kind of problem requires more than just one piece of test data.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

Ok,here's a mockup.

The sheet "Processing"column D contains the Vlookup that needs to be modified so that text characters are removed before storing the value.
If a solution works... the formula in the column "Sales price" will show the correct calculated values.

Hi pluginguin,

Try the following in cell D2:

Please Login or Register  to view this content.

That should extract the number but as text. To convert it to a number use:

Please Login or Register  to view this content.

This should work but I can't test it because my machine uses periods instead of commas in numbers.

If you need the commas changing to periods as well let me know.

Hope this helps,

Snook

Yes, your last formula does the trick, thanks...

But I suspect that I will get lists from other suppliers that also have numbers & text as a value, but it might not be in the same format.
So not all values will have text starting with "€".... could be an entirely other character.

So I am looking for a more general way of separating text from numbers in combination with a vlookup.
Can I replace the "€" bit in the formula with a dynamic value for any text character?

This will extract the number from the beginning of an alphanumeric text string:

=-LOOKUP(1;-LEFT(D2,ROW($1:$100)))

To get it to work with the decimal marker used in Ireland, I changed the , to . With your local settings it should be fine.