How to convert currency to text with formatting or vice versa in Excel?

Applying Excel formulas is a handy method to convert currency-formatted numbers to text while keeping the visual format unchanged. This approach is best used for small to medium data sets and where dynamic link between original data and the output format is preferred (so the conversion updates if the original number changes).

For example, to convert numeric currency values in column B to formatted text in column C:

Select a blank cell beside the currency column (e.g., Cell C2), and enter the following formula:

=TEXT(B2,"$ #,##0.00 ;")

Here, B2 refers to the cell containing the currency you want to convert. If your worksheet uses a different currency symbol or format, you can customize the format string accordingly (for example, use "€ #,##0.00" for Euros).

After entering the formula, press Enter to confirm. Then, drag the cell’s AutoFill Handle down to copy the formula for the rest of the range you want to convert.

Afterwards, all currency numbers will be transformed into text strings with the same currency formatting, as shown below:

Tips:

• Check for consistency in your data (no mixed types in the column) before applying the formula for accurate results.
• If you need static values instead of formulas, copy the results and use Paste Special > Values to paste over the formulas.

Sometimes, you may need to convert currency numbers to text with formatting and overwrite the original numbers in place. When working with large data ranges or when batch modification is needed, the Add Text feature of Kutools for Excel allows you to efficiently complete this task without complicated formulas.

1. Select the currency numbers you wish to convert, then go to Kutools > Text > Add Text.

2. In the Add Text dialog box, enter ‘$ into the Text field, select Before first character, and click OK.

This will instantly convert all the selected currency numbers into text values, retaining the currency symbol as part of the cell content.

Advantages: This method is very suitable for batch operations and can overwrite the original values directly. It also supports choice of other prefix or suffix text, allowing more flexibility than basic formulas.

Caution: Be careful with cells already formatted as text or with leading apostrophes, as repeated operations may result in unwanted characters.

If you want to convert currency text strings to numbers in Excel, the VALUE formula provides a straightforward way to achieve this quickly. This method is especially suitable for smaller datasets or when you want to retain a formula connection to your source data, enabling you to further process results dynamically.

Select a blank cell beside the column with currency text strings (for example, C2). Enter the following formula:

=VALUE(B2)

Here, B2 is the cell containing the currency text (such as "$1,234.56") you want to convert to a number. After entering the formula, press Enter. Drag the formula down to fill as many rows as you need for conversion.

Now, the text entries with currency symbols will be changed into numbers that can participate in calculations. The output should look like this:

To further format the resulting numbers as currency values:

• Select the result numbers, right-click and choose Format Cells.
• In the dialog box, pick the Currency category and specify your desired currency format.

Note: The VALUE() formula may not handle non-standard formats or symbols from certain regions. If you get #VALUE! errors, double-check your original data for extra spaces or unusual characters.

Convert dates to text or text formatting date to real date

Convert currencies in Excel