Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract format from cell (2002)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract format from cell (2002)

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

    <pre>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</pre>


    Use in a cell like:
    =getcurrency(A1)

    Steve

  3. #3
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract format from cell (2002)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Extract format from cell (2002)

    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

  5. #5
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract format from cell (2002)

    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:
    <pre>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
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •