Extract format from cell (2002)

Topic

New Reply

I have a spreadsheet with about 20,000 rows that came from a database. One of the columns has currency values from different types of currencies (e.g. US $, Euros, etc.) Each of the cells has a custom format that indicates what type of currency it is (e.g. [$EUR] #,##0.00_);([$EUR] #,##0.00) ). What I need to be able to do is extract the currency type from the fomat into another column, so I can determine which currency is appropriate for each cell. Does anyone have an idea about how to do this?

Thanks,
Brent

Reply | Quote

Replies

This function will get the “currency” within the brackets of the formula. If there are no brackets it assumes the dollar.

Option Explicit
Function GetCurrency(rng As Range)
    Dim sForm As String
    Dim iStart As Integer
    Dim iEnd As Integer
    sForm = rng.Cells(1).NumberFormat
    iStart = InStr(sForm, "[")
    iEnd = InStr(sForm, "]")
    If iStart = 0 Then
        GetCurrency = "$"
    Else
        GetCurrency = Mid(sForm, iStart + 1, iEnd - iStart - 2)
    End If
End Function

Use in a cell like:
=getcurrency(A1)

Steve

Reply | Quote

Thanks Steve. That was exactly what I needed. It turned out that I had to make a couple of minor modifications to get it to work (some of the currency names were enclosed in quotes rather than brackets), and the spacing was one off, but other than that it was perfect.

Thanks,
Brent

Reply | Quote

Thanks Steve. That was exactly what I needed. It turned out that I had to make a couple of minor modifications to get it to work (some of the currency names were enclosed in quotes rather than brackets), and the spacing was one off, but other than that it was perfect.

Thanks,
Brent

Reply | Quote

Would you mind posting your modifications to the board?

I am just thinking in case the question comes up from another user later on. When he/she finds this thread, he/she does not have to reinvent your code and its modifications.

Thanks,
Steve

Reply | Quote

I was wrong about the format initially (it uses quotes instead of square brackets). Here’s an example of the actual format: #,##0.00 “CHF”;-#,##0.00 “CHF”;

Here’s the modified code:

Function GetCurrency(rng As Range)
    Dim sForm As String
    Dim iStart As Integer
    Dim iEnd As Integer
    sForm = rng.Cells(1).NumberFormat
    iStart = InStr(sForm, """")
    iEnd = InStr(iStart + 1, sForm, """")
    If iStart = 0 Then
        GetCurrency = "$"
    Else
        GetCurrency = Mid(sForm, iStart + 1, iEnd - iStart - 1)
    End If
End Function

Reply | Quote

I was wrong about the format initially (it uses quotes instead of square brackets). Here’s an example of the actual format: #,##0.00 “CHF”;-#,##0.00 “CHF”;

Here’s the modified code:

Function GetCurrency(rng As Range)
    Dim sForm As String
    Dim iStart As Integer
    Dim iEnd As Integer
    sForm = rng.Cells(1).NumberFormat
    iStart = InStr(sForm, """")
    iEnd = InStr(iStart + 1, sForm, """")
    If iStart = 0 Then
        GetCurrency = "$"
    Else
        GetCurrency = Mid(sForm, iStart + 1, iEnd - iStart - 1)
    End If
End Function

Reply | Quote

Would you mind posting your modifications to the board?

I am just thinking in case the question comes up from another user later on. When he/she finds this thread, he/she does not have to reinvent your code and its modifications.

Thanks,
Steve

Reply | Quote

This function will get the “currency” within the brackets of the formula. If there are no brackets it assumes the dollar.

Option Explicit
Function GetCurrency(rng As Range)
    Dim sForm As String
    Dim iStart As Integer
    Dim iEnd As Integer
    sForm = rng.Cells(1).NumberFormat
    iStart = InStr(sForm, "[")
    iEnd = InStr(sForm, "]")
    If iStart = 0 Then
        GetCurrency = "$"
    Else
        GetCurrency = Mid(sForm, iStart + 1, iEnd - iStart - 2)
    End If
End Function

Use in a cell like:
=getcurrency(A1)

Steve

Reply | Quote