'Filter/Clean' Cells; 2 questions

Hello,

I have the following two questions regarding excel:

Question 1

I have a collumn with over 3000 cells containing amounts in different ccurrencies, only problem is that the cells are formatted as text instead of currency and that lay-out is different for all cells. So for example I have the following cells (the cell contains the text between brackets):

"USD 31,000"
"CHF 24.000 "
"DEM 1,000 "

So the cells contain emtpy spaces, commas, points, letters and numbers. Because I am using the cells in other formulas I want them all to be similar and I only need the numbers. How can I extract only the numbers from these cells? So I get in the previous example:

"31,000"
"24,000"
"1,000"

Question 2:

I also have a collumn which contains 3000+ cells looking like this (between brackets):

"EUR 34,148 -USD 32,000- GBP 21,334 "
"EUR 16,007 - USD 15,000 - GBP 10,000"
"EUR 18,675 - USD 17,500 - GBP 11,667 "
etc.

Now, for this column I am only interested in the EURO value, but again, I want them in the same format as question 1 (beware: again the layout is not the same for all cells, so some contain additional spaces). So how can I convert the above to:

"34,148"
"16,007"
"18,675"

Thank you for your time and help!

Question 1

Try this:
=RIGHT(A1,LEN(A1)-FIND(" ",A1))

Question 2

I'm assuming the separator is [space][dash][space], and not just a [dash].

If Euro is always at the front, try this:
=MID(A1,FIND(" ",A1)+1,FIND("~",SUBSTITUTE(A1," ","~",2))-FIND(" ",A1)-1)
Or
=MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1)

If Euro can be anywhere, try this:
=MID(A1,FIND("EUR ",A1)+4,FIND(" ",A1,FIND("EUR ",A1)+4)-FIND("EUR ",A1)-4)

The "~" isn't a magical character... it's just an infrequently used one that is good for this because it doesn't generally conflict with other characters. If you have a "~" in your data anywhere, you should find another character to use instead that doesn't appear.

Scott

Thank you for your help, but it does not work (yet .

For question 1, I have typed:

=RIGHT(D2;LEN(D2))-FIND(" ";D2)

and I get: "#VALUE!"
(I replaced all commas with ; because I think that is so with my excel version ,but with commas it doesn't work I get another error: "The formula you typed contains an error")


For question 2, I have typed:

=MID(D16;FIND("EUR ";D16)+4;FIND(" ";D16;FIND("EUR ";D16)+4)-FIND("EUR ";D16)-4)

and I also get: "#VALUE!".

I think it is the same error, but I don't know what....
Please help me!

Could anyone help me with tehse two questions above? I am still getting the error which I explained in my previous post?

Thank you very much for your time and trouble!

Regards

Originally Posted by EHS

Could anyone help me with tehse two questions above? I am still getting the error which I explained in my previous post?

Thank you very much for your time and trouble!

Regards

for your first question I suppose that you have a space between number and letters like USD[space]31,000 and not between the numbers like 31[space]000.
try following procedure to remove text other than numbers (keep backup file before doing so)

select your column or range that contains data as in your first question.
press Ctrl+H
in Find what: type * and press space bar once
in Replace with: leave it blank
press Replace All

before going ahead let me know that do you type commas (,) and points (.) in figures?
like 31,000 or 24.000 (do you type comma and point)?

regarding your second question what do you mean by "Now, for this column I am only interested in the EURO value" and also make it clear that do you enter the whole text in one column like this
A1 = EUR 34,148 -USD 32,000- GBP 21,334

Regards.

Ok, I have solved my problem partially. Meaning, I now I have a column with "EUR 13,405 " which I want to reduce to "13,405".

How to do that? Please help me out here!

Thanks & Regards