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?
- Select the column of text and choose Data, then Text to Columns.
- Pick Delimited, click Next, and tick Space only.
- Set the Destination to the next empty column so the original text stays safe.
- 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 A | Code | Amount | Status |
|---|---|---|---|
| USD 12.50 | USD | 12.5 | OK |
| eur 40 | EUR | 40 | OK |
| SGD 1,200.00 | SGD | 1200 | OK |
| USD12.50 | USD | 12.5 | OK |
| $ 12.50 | $ 1 | Check amount | Unknown 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?
| Symptom | Likely cause | Fix |
|---|---|---|
| #VALUE! in the amount column | A non-breaking space or a symbol in the text | Wrap the text in SUBSTITUTE(A2,CHAR(160)," ") |
| Amount of 12.5 becomes 125 | Decimal and thousands separators swapped by regional settings | Check your decimal separator in the system settings |
| Code shows as US or USD1 | Code is not always three letters before the space | Split on the first space with FIND(" ",A2) |
| Amount stays left-aligned | Still text | Use VALUE or multiply by 1 |
A short checklist
- Confirm every row follows the same pattern.
- Split into code and amount columns.
- Run the unknown-code and non-number checks.
- Only then use SUMIFS or a pivot table by currency.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


