Give currency of formatted cell

[joeypar]

Hey,

I've got a bunch of data from a BI source, but they havent provided the currency. The values are formatted in the correct currency, so i want to write a formula in another cell that will tell me what currency each line is in. Any idea how i can do this? I'm sure i've done this before but completely forgotten and cant find anything online

 

[Special-K99]

Example data ?

"The values are formatted in the correct currency,"

Do you mean the values coming in are text? Or the cells are numbers and the cell format is "Currency" ?

 

[joeypar]

Cells are numbers and formatted as currency

 

[Special-K99]

Doesnt look like you can do that with a formula.
I found this UDF that produces different output for different currencie

Function ccyform(ByRef c As Range)
x = c.NumberFormat
For i = 1 To Len(x)
    If Not Mid(x, i, 1) = " " Then
        y = y + 1
    Else
        Exit For
    End If
   Next
ccyform = Left(x, y)
    
End Function

 

[joeypar]

Thanks! It didnt work exactly, but i was able to use a part of it to get the data i needed. I just used this to get the full format, and then map the formats to currencies:


Function CurrencyFormat(ByRef c As Range)


CurrencyFormat = c.NumberFormat

End Function