Identify currency format and store abbreviated currency name in adjacent cell

  • Hello,


    I have a spreadsheet with a column for total amounts which are randomly formatted as standard number or $ currency or Euro currency. I need to find a way to identify the currency used in the cell and then store the abbreviated currency name in the adjacent cell, e.g if the number in the Total column is $, then insert "USD" in the CURR column. The formula would need to evaluate and convert 3 possible formats: plain number = RSD, $ format = USD and E (Euro symbol) = EUR



    [TABLE="class: grid, width: 500, align: left"]

    [tr]


    [td]

    Total

    [/td]


    [td]

    CURR

    [/td]


    [/tr]


    [tr]


    [td]

    $500.00

    [/td]


    [td]

    USD

    [/td]


    [/tr]


    [tr]


    [td]

    E26.33

    [/td]


    [td]

    EUR

    [/td]


    [/tr]


    [tr]


    [td]

    255.22

    [/td]


    [td]

    RSD

    [/td]


    [/tr]


    [/TABLE]









    Thanks very much,


    Zdjbel

  • Re: Identify currency format and store abbreviated currency name in adjacent cell


    Does this do what you want?


    =IF(LEFT(A2,1)="$","USD",IF(LEFT(A2,1)="E","EUR","RSD"))


    Windy

  • Re: Identify currency format and store abbreviated currency name in adjacent cell


    Hi Windy,


    That works if the first character of A2 is a text type character, but my issue is that the currency in the cell is determined through formatting (see screenshot), not as a text character. The users who input the data into the cell format it in accordance with their local currency. What I'm trying to achieve is to identify the format of cell A2, and then execute the rest of the fomula as you wrote it.



    [ATTACH=CONFIG]60275[/ATTACH]
    Thanks very much,


    Zdjbel

  • Re: Identify currency format and store abbreviated currency name in adjacent cell


    I found a solution.


    1. A custom function needs to be inserted into a standard module. The function and instructions are available at this link: http://www.pcreview.co.uk/foru…mber-format-t3780863.html
    2. In the adjacent cell (B1 if the number for evaluation is in A1) call the function. That will convert the contents of A1 from currency or number to text format
    3. Insert Windy58's formula =IF(LEFT(A2,1)="$","USD",IF(LEFT(A2,1)="E","EUR","RSD")) in the next adjacent cell, e.g. C1, to extract the currency abbreviation(s).

  • Re: Identify currency format and store abbreviated currency name in adjacent cell


    or use:


    Code
    1. Function F_val(c00 As Range)
    2. F_val = Choose(InStr("€$", Left(c00.Text, 1)) + 1, "RSD", "EUR", "USD")
    3. End Function
  • Re: Identify currency format and store abbreviated currency name in adjacent cell


    Hello snb,


    Thank you, this is a far more elegant solution.


    zdjbel