Spreadsheets

Split currency code from amount text like "USD 12.50"

Split text such as USD 12.50 into a currency code and a numeric amount in Excel using LEFT, MID, VALUE, Text to Columns or Flash Fill, then test for unknown codes.

Editorial illustration for Split currency code from amount text like "USD 12.50"

If your cells hold text like USD 12.50, take the code with =LEFT(A2,3) and the number with =VALUE(MID(A2,5,20)). For a one-off cleanup, Data, Text to Columns with a space as the delimiter splits the same cells without formulas. Then run a check so no row has a currency code you have not set up.

Which formulas split the text?

Say A2 holds USD 12.50 and the code is always three letters, followed by one space.

  • Currency code: =UPPER(LEFT(TRIM(A2),3))
  • Amount: =VALUE(TRIM(MID(TRIM(A2),4,30)))
  • Amount when the code comes last, as in 12.50 USD: =VALUE(LEFT(TRIM(A2),LEN(TRIM(A2))-3)) and the code with =RIGHT(TRIM(A2),3)
  • TRIM removes extra spaces; UPPER turns usd into USD.

MID(text, start, length) starts at character 4 here, which is the space, and TRIM then removes it. VALUE converts the leftover text into a real number so it can be summed.

Do I get the same result with Text to Columns?

  1. Select the column of text and choose Data, then Text to Columns.
  2. Pick Delimited, click Next, and tick Space only.
  3. Set the Destination to the next empty column so the original text stays safe.
  4. Click Finish, then check that the amount column is right-aligned (a sign of real numbers).

Text to Columns gives fixed results, not formulas. It is fast for a one-time import, but if you will paste new data each month, formulas are easier to repeat.

What about Flash Fill?

In the cell next to the first row type the code by hand (USD), start the second row, and press Ctrl+E in Excel 2013 or later. Excel guesses the pattern and fills the rest. Flash Fill is quick but it guesses, so scroll through the results, especially for rows with unusual spacing or lower-case codes.

How do I test for unknown currency codes?

Keep a list of valid codes on a Rates or Lists sheet, say in Lists!$A$2:$A$20. Then add a status column:

  • =IF(COUNTIF(Lists!$A$2:$A$20,B2)=0,"Unknown code","OK")
  • =IFERROR(VALUE(TRIM(MID(TRIM(A2),4,30))),"Check amount") to catch amounts that are not numbers.
  • =COUNTIF($C$2:$C$500,"Unknown code") at the top of the sheet; it should read 0 before you total anything.

What do the results look like on messy text?

Here is how the formulas above behave on a few hypothetical cells. The last row shows why the check column matters.

Text in ACodeAmountStatus
USD 12.50USD12.5OK
eur 40EUR40OK
SGD 1,200.00SGD1200OK
USD12.50USD12.5OK
$ 12.50$ 1Check amountUnknown code

Rows like the last one need a manual fix or a SUBSTITUTE step that swaps the symbol for a code. A symbol such as $ is ambiguous in any case, since several currencies use it, so decide the code yourself from the receipt instead of guessing by formula.

Is there a single formula in newer Excel?

In recent Microsoft 365 builds, TEXTSPLIT can do the split in one step: =TEXTSPLIT(A2," ") puts the code and the amount in two neighbouring cells, but the amount still arrives as text. Convert it with =VALUE(INDEX(TEXTSPLIT(A2," "),2)). If TEXTSPLIT is not in your version, stay with LEFT and MID, which work everywhere.

What typically goes wrong?

SymptomLikely causeFix
#VALUE! in the amount columnA non-breaking space or a symbol in the textWrap the text in SUBSTITUTE(A2,CHAR(160)," ")
Amount of 12.5 becomes 125Decimal and thousands separators swapped by regional settingsCheck your decimal separator in the system settings
Code shows as US or USD1Code is not always three letters before the spaceSplit on the first space with FIND(" ",A2)
Amount stays left-alignedStill textUse VALUE or multiply by 1

A short checklist

  1. Confirm every row follows the same pattern.
  2. Split into code and amount columns.
  3. Run the unknown-code and non-number checks.
  4. Only then use SUMIFS or a pivot table by currency.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports