Excel Formula to Extract Currency Symbols Using LEFT and UNICODE

Parsing international financial data in Excel often leads to frustration when isolating inconsistent currency symbols. When consolidating portfolios from diverse funding sources like EU grants or global venture capital, standard formatting often breaks down. Extracting these symbols dynamically grants analysts the ability to seamlessly categorize regional cash flows. However, a key stipulation is that Excel's LEFT function must align with UNICODE values to accurately identify symbols like £ (Unicode 163) or € (Unicode 8364). Below, we outline the exact formulas to automate this extraction process seamlessly.

In international business and financial reporting, spreadsheets often contain data with mixed currencies. You might have a column with values like $150.00, €200.00, and £350.00. To perform accurate conversions, grouping, or reporting, you must first extract the currency symbol from these cells. This task can be surprisingly tricky depending on how the data is stored in Excel.

In this comprehensive guide, we will explore how to construct an Excel formula to extract currency symbols using the LEFT and UNICODE functions, understand why standard formulas sometimes fail on formatted numbers, and look at advanced workarounds to solve these issues.

The Core Challenge: Text vs. Number Formatting

Before writing any formulas, it is crucial to understand how Excel handles currency. There are two ways a currency symbol appears in a cell:

1. As Literal Text: The cell value is stored as text (e.g., "$150.00"). In this case, Excel treats the currency symbol as a character, and we can easily manipulate it using text functions like LEFT, MID, and LEN.
2. As Number Formatting: The cell value is stored as a raw number (e.g., 150.00), but Excel's cell formatting is applied to display it with a symbol (e.g., $150.00). In this scenario, text functions like LEFT will fail because they can only see the underlying raw number (1), completely ignoring the cosmetic symbol.

We will address both scenarios below, starting with the text-based extraction using LEFT and UNICODE.

Method 1: Extracting Currency from Text Strings using LEFT

If your currency symbols are imported as raw text, extracting them is straightforward. Since currency symbols are typically placed at the very beginning of the value, you can use the LEFT function to pull the first character.

The Basic LEFT Formula

To extract the first character from cell A2, use the following formula:

=LEFT(A2, 1)

If cell A2 contains the text string $500, this formula returns $. If it contains €1,200, it returns €.

Handling Spaces and Multi-Character Codes

Sometimes, currency identifiers are not single-character symbols but three-letter codes (like USD, EUR, or GBP) followed by a space. For example: USD 500.

If your dataset uses three-letter codes, you can adjust the length parameter of the LEFT function:

=LEFT(A2, 3)

If your dataset contains a mix of single-character symbols and three-letter codes, you can write a dynamic formula using IF, ISNUMBER, and SEARCH, or simply find the position of the first space:

=IF(ISERR(FIND(" ", A2)), LEFT(A2, 1), LEFT(A2, FIND(" ", A2) - 1))

Method 2: Identifying the Currency Symbol with UNICODE

Once you extract a currency symbol using the LEFT function, you might want your spreadsheet to automatically identify what currency it represents. This is where the UNICODE and UNICHAR functions become incredibly useful.

Every character in Excel has a unique numeric code point designated by the Unicode standard. By extracting the symbol and checking its Unicode number, you can create bulletproof logic that avoids regional character encoding issues.

How UNICODE Works in Excel

The UNICODE function returns the numeric code point corresponding to the first character of the text string:

=UNICODE(text)

By nesting our LEFT formula inside the UNICODE function, we can find the exact code for the currency symbol in cell A2:

=UNICODE(LEFT(A2, 1))

Common Currency Unicode Reference Table

To help you map these code points to their respective currencies, refer to the table below:

Creating a Dynamic Identifier

With this knowledge, you can build a nested IFS or SWITCH formula to identify the currency name automatically based on the extracted symbol's Unicode value:

=SWITCH(UNICODE(LEFT(A2, 1)), 36, "USD", 163, "GBP", 8364, "EUR", 165, "JPY", 8377, "INR", "Unknown")

This approach is highly reliable because it processes numeric identifiers, which eliminates errors caused by regional system formats or font styling differences.

Method 3: Extracting Symbols from Truly Formatted Numbers

As mentioned earlier, if your cells are true numbers formatted as currency, using =LEFT(A2, 1) will fail. For example, if cell A2 contains the numeric value 100.50 formatted as $100.50, =LEFT(A2, 1) will return 1 because it evaluates the raw number 100.5.

To extract the currency symbol from formatted numbers, you have two primary options: using the TEXT function or writing a custom VBA function.

Option A: The TEXT Function Workaround

If you know what format is applied to your cells, you can force Excel to convert the formatted number to text inside the formula using the TEXT function:

=LEFT(TEXT(A2, "$#,##0.00"), 1)

However, this method requires you to know the currency format in advance, which defeats the purpose of dynamically extracting unknown currency symbols.

Option B: Using VBA to Read the Displayed Formatting

To dynamically capture whatever currency symbol is currently displayed on your screen via number formatting, you can create a simple User-Defined Function (UDF) in VBA. This function reads the cell's formatted text rather than its raw numeric value.

Follow these steps to set it up:

1. Press ALT + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the following code into the module window:

Function GetCurrencySymbol(Cell As Range) As String
    Dim formattedText As String
    Dim firstChar As String
    
    ' Get the text exactly as it is displayed in the cell
    formattedText = Trim(Cell.Text)
    
    ' Extract the first character
    firstChar = Left(formattedText, 1)
    
    ' If the first character is a number or minus sign, check for trailing currencies
    If IsNumeric(firstChar) Or firstChar = "-" Then
        ' Check if there is a non-numeric character at the end (e.g., 100 €)
        Dim lastChar As String
        lastChar = Right(formattedText, 1)
        If Not IsNumeric(lastChar) Then
            GetCurrencySymbol = lastChar
        Else
            GetCurrencySymbol = "No Symbol"
        End If
    Else
        GetCurrencySymbol = firstChar
    End If
End Function

Once you save this module and return to your worksheet, you can use your custom formula just like any built-in Excel function:

=GetCurrencySymbol(A2)

If you want to find the Unicode value of the symbol retrieved by this custom VBA function, you can nest it directly:

=UNICODE(GetCurrencySymbol(A2))

Summary of Best Practices

• Use standard text formulas (LEFT, MID) if your financial data was imported as text and contains literal currency symbols.
• Leverage UNICODE values to build logical tests and conversions, protecting your formulas from cross-platform encoding errors.
• Use VBA / UDFs if you are working with native Excel number formatting, as standard worksheet formulas cannot natively read custom cell formats without explicit manual instructions.

By mastering these techniques, you can easily clean up multi-currency spreadsheets, automate currency conversions, and build highly professional global financial models in Excel.