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

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"

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.

" wrote:

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

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)

"Lori" wrote:

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.

" wrote:

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

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

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

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


" wrote:

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

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

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

=CELL("format",A2)

"Joel" wrote:

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)

"Lori" wrote:

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.

" wrote:

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

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

wrote in message
oups.com...
This might help.
The custom formats that have been used are as follows

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