Identifying currencies

[ciaran.hudson]

Hi,

In a column of data, in excel, I have a mix of currencies.
Some cells contain US$ and some contain Euro like below:
$123.00
345.00 Euro

I need to identify which cells are in Euro so that I can apply a
currency conversion, but cannot find a solution.

I've tried using FIND("$",CellX), but to identify the US$ amounts, but
this did not work as when I click into CellX I see that it does not
actually contain "$"; the formatting has presented it as US$.

I've also tried changing the column format to TEXT, but this did not
work because I lost the currency symbols.

Any help would be greatly appreciated.

Thanks,

Ciarán

 

[ciaran.hudson]

This might help someone identify how to differentiate between the
different fromats.
Using the example above, the respective custom formats of each cell
are as follows.

"$" #,##0.00
#,##0.00 "EURO"

 

[Lori]

To make a copy of the column as text, choose Edit > Office clipboard then:
Copy the column, format a column next to it as text, and click the paste
icon.
[for small ranges you may need to then undo and paste special as text]

Equivalently copy the column to notepad, format the next column as text and
paste back from notepad.

 

[Joel]

You can easily make a cell text by placing a single quote in the fron of the
data. Use tis to put a dollar sign in front of numbers '$1.25.

Why don't you just look for Euro and convert these numbers and don't do
anything iff you don't find Euro.

if(FIND("Euro",CellX),Conversion * Cellx, Cellx)

To make a copy of the column as text, choose Edit > Office clipboard then:
Copy the column, format a column next to it as text, and click the paste
icon.
[for small ranges you may need to then undo and paste special as text]

Equivalently copy the column to notepad, format the next column as text and
paste back from notepad.

Hi,

In a column of data, in excel, I have a mix of currencies.
Some cells contain US$ and some contain Euro like below:
$123.00
345.00 Euro

I need to identify which cells are in Euro so that I can apply a
currency conversion, but cannot find a solution.

I've tried using FIND("$",CellX), but to identify the US$ amounts, but
this did not work as when I click into CellX I see that it does not
actually contain "$"; the formatting has presented it as US$.

I've also tried changing the column format to TEXT, but this did not
work because I lost the currency symbols.

Any help would be greatly appreciated.

Thanks,

Ciarán

 

[ciaran.hudson]

This might help.
The custom formats that have been used are as follows

"$" #,##0.00
#,##0.00 "EURO"

 

[Joel]

Help is a simple function that will return the text of the cell incluing $
and Euro. You can then use find to actually find the $.

Function ReturnText(Target As Range) As String
ReturnText = Target.Text
End Function

 

[Lori]

If there is only $ and Euro, you may be able to distinguish them by filling
down:

=CELL("format",A2)

You can easily make a cell text by placing a single quote in the fron of the
data. Use tis to put a dollar sign in front of numbers '$1.25.

Why don't you just look for Euro and convert these numbers and don't do
anything iff you don't find Euro.

if(FIND("Euro",CellX),Conversion * Cellx, Cellx)

To make a copy of the column as text, choose Edit > Office clipboard then:
Copy the column, format a column next to it as text, and click the paste
icon.
[for small ranges you may need to then undo and paste special as text]

Equivalently copy the column to notepad, format the next column as text and
paste back from notepad.

Hi,

In a column of data, in excel, I have a mix of currencies.
Some cells contain US$ and some contain Euro like below:
$123.00
345.00 Euro

I need to identify which cells are in Euro so that I can apply a
currency conversion, but cannot find a solution.

I've tried using FIND("$",CellX), but to identify the US$ amounts, but
this did not work as when I click into CellX I see that it does not
actually contain "$"; the formatting has presented it as US$.

I've also tried changing the column format to TEXT, but this did not
work because I lost the currency symbols.

Any help would be greatly appreciated.

Thanks,

Ciarán

 

[Zone]

Maybe I'm missing something, but if the number format is the only difference
in the numbers, couldn't you just use a relatively simple macro like this to
convert everything to US$ ?
If your numbers are in column A and column B is available (empty), then
format column B for currency. Copy this sub and paste it in a standard
module. Then run the sub. HTH, James

Sub Chg()
Dim c As Range
Const CONVRT = 1.345 'change to correct conversion factor
Columns("b").ClearContents
For Each c In Range("a1:a" & Cells(Rows.Count, "a").End(xlUp).Row)
If c.NumberFormat = "#,##0.00 " & Chr(34) & "EURO" & Chr(34) Then
c.Offset(0, 1) = c.Value * CONVRT
Else
c.Offset(0, 1) = c.Value
End If
Next c
End Sub