formula to make currency formatting useful
 

situation: a woman i work with pays thousands of invoices a year and records
them in a spreadsheet. When they are in different currencies, she doesn't
record the currency type as a value in a column, but merely formats the cell
as the currency type. I need to be able to eventually convert the amounts
into USD, but i guess i am looking for an interim step to create a column
that takes the formatting and converts to a currency code. I can see at
least 4 different currencies represented. Can anyone provide a formula that
can help me get to my end goal?
thanks!
N

formula to make currency formatting useful
 

You can install this short UDF into the VBE first (Alt+F11, Insert - Module,
paste the following code in):

Function XFORMAT(YourCell As Range) As String
XFORMAT = YourCell.NumberFormat
End Function


And then in your workbook, the formula would be:
=XFORMAT(A2)

This will spit out all the information regarding the number formatting. You
will then prb be able to manipulate it using some of the text functions
(LEFT, FIND, MID, etc) to dfetermine what currency is actually being used.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nipsey Russell" wrote:

situation: a woman i work with pays thousands of invoices a year and records
them in a spreadsheet. When they are in different currencies, she doesn't
record the currency type as a value in a column, but merely formats the cell
as the currency type. I need to be able to eventually convert the amounts
into USD, but i guess i am looking for an interim step to create a column
that takes the formatting and converts to a currency code. I can see at
least 4 different currencies represented. Can anyone provide a formula that
can help me get to my end goal?
thanks!
N

formula to make currency formatting useful
 

Maybe this will give you a directon.....

Sub CheckCurrencyFormat()
If Selection.NumberFormat = "$#,##0.00" Then
Selection.Offset(0, 1).Value = "US Dollars"
Else
If Selection.NumberFormat = "[$£-809]#,##0.00" Then
Selection.Offset(0, 1).Value = "English Pounds"
Else
If Selection.NumberFormat = "_-* #,##0.00 [$‚¬-813]_-;-* #,##0.00
[$‚¬-813]_-;_-* ""-""?? [$‚¬-813]_-;_-@_-" Then
Selection.Offset(0, 1).Value = "Euro"
Else
Selection.Offset(0, 1).Value = "Unrecognized Format"
End If
End If
End If
End Sub

Vaya con Dios,
Chuck, CABGx3

"Nipsey Russell" wrote:

situation: a woman i work with pays thousands of invoices a year and records
them in a spreadsheet. When they are in different currencies, she doesn't
record the currency type as a value in a column, but merely formats the cell
as the currency type. I need to be able to eventually convert the amounts
into USD, but i guess i am looking for an interim step to create a column
that takes the formatting and converts to a currency code. I can see at
least 4 different currencies represented. Can anyone provide a formula that
can help me get to my end goal?
thanks!
N

formula to make currency formatting useful
 

I had a similar problem and used a macro to get the formatting information.

Once you have that you can write a formula to do the conversion

This macro would get the currency format info for all the cells in column C
and dump it into column D.

Sub curformat()

Dim cf As String
lastrow = Range("C10000").End(xlUp).Row
For A = 1 To lastrow
cf = Cells(A, 3).NumberFormat
Cells(A, 4) = cf
Next A

End Sub

The results look like this
$#,##0.00
#,##0.00 [$‚¬-1]

Since these are text strings you can search for thing like the $ or ‚¬
character and perform the appropriate conversion.

You could write some large formula to check for all conditions, but if you
sort your info by the format type string you could group them together and
use a simplier formula by grouping.

When I had to fix my data I used the format type string to add a column for
currency type. Then I had a formula to do a vlookup for a conversion value
for the entered currency type, multiply this by the entered value and result
in a value in USD. Then I just had everyone select a currency type when
entering info.




--
If this helps, please remember to click yes.


"Nipsey Russell" wrote:

situation: a woman i work with pays thousands of invoices a year and records
them in a spreadsheet. When they are in different currencies, she doesn't
record the currency type as a value in a column, but merely formats the cell
as the currency type. I need to be able to eventually convert the amounts
into USD, but i guess i am looking for an interim step to create a column
that takes the formatting and converts to a currency code. I can see at
least 4 different currencies represented. Can anyone provide a formula that
can help me get to my end goal?
thanks!
N

formula to make currency formatting useful
 

thanks, i am new to UDFs & VBE, i struggled with all of these suggestions,
but ultimately this xformat UDF helped!

"Luke M" wrote:

You can install this short UDF into the VBE first (Alt+F11, Insert - Module,
paste the following code in):

Function XFORMAT(YourCell As Range) As String
XFORMAT = YourCell.NumberFormat
End Function


And then in your workbook, the formula would be:
=XFORMAT(A2)

This will spit out all the information regarding the number formatting. You
will then prb be able to manipulate it using some of the text functions
(LEFT, FIND, MID, etc) to dfetermine what currency is actually being used.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Nipsey Russell" wrote:

situation: a woman i work with pays thousands of invoices a year and records
them in a spreadsheet. When they are in different currencies, she doesn't
record the currency type as a value in a column, but merely formats the cell
as the currency type. I need to be able to eventually convert the amounts
into USD, but i guess i am looking for an interim step to create a column
that takes the formatting and converts to a currency code. I can see at
least 4 different currencies represented. Can anyone provide a formula that
can help me get to my end goal?
thanks!
N