Excel Concatenate Number and String: Formula Example

This Excel concatenate number and string formula example helps you:

• Join a number and a string;
• While keeping proper number formatting.

Excel Concatenate Number and String Formula Template

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-number-string/

'Concatenate string and number
=String&TEXT(Number,"NumberFormat")

'Concatenate number and string
=TEXT(Number,"NumberFormat")&String

For these purposes:

• String is the string you concatenate.
• Number is the number you concatenate.
• NumberFormat is the applicable number format code. You can use the number format code strings from the Number tab of the Format Cells dialog box.

Excel Concatenate Number and String Example Formula

The Excel concatenate number and string example formula below:

• Joins:
  • The string stored in cell B6; and
  • The number stored in cell B7;
• While formatting the number (stored in cell B7) as currency (“$#,##0.00”), with the following characteristics:
  • Preceded by the Dollar sign ($);
  • A comma (,) as thousands separator; and
  • 2 decimal places (.00).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-concatenate-number-string/

=B6&TEXT(B7,"$#,##0.00")

The image at the top of this post shows the results I obtain with this Excel concatenate number and string example formula.

Excel Concatenate Number and String Explanation

The ampersand text concatenation operator (&):

• Concatenates items; and
• Returns a single string with the concatenated items.

Concatenated items (therefore) become strings. In other words: When you concatenate a number and a string:

• The number:
  • Becomes text; and
  • No longer works as a numeric value.
• Excel displays an unformatted (raw) numeric value. For example, “9999.99” instead of $9,999.99)

Use the TEXT function to control how a concatenated number is displayed when creating an Excel concatenate number and string formula. The TEXT function allows you to control how a number is displayed by applying a number format (you specify).

The ampersand text concatenation operator (&) is not the only way to concatenate items. You can find more Excel concatenate formula examples in the More Excel Concatenate Formula Examples section.

More Excel Concatenate Formula Examples

This formula example is part of a more comprehensive series of Excel concatenate formula examples.

• Excel Concatenate Strings: Click here to open.
• Excel Concatenate Number and String: Click here to open.
• Excel Concatenate Date: Click here to open.
• Excel Concatenate with Space: Click here to open.
• Excel Concatenate New Line: Click here to open.
• Excel Concatenate Double Quote: Click here to open.
• Excel Concatenate Multiple Cells with Comma: Click here to open.

More Excel Training Materials and Resources

You can find more Excel Tutorials (including other formula examples) in the organized Tutorials Archive: Click here to visit the Archives.

If you want to ease all your operations and processes, you may be interested in Someka's ready-to-use Excel and Google Sheets templates. Someka's template collections cover, among others:

• Project Management;
• Human Resources;
• Sales and Marketing;
• Inventory Management;
• Calendars and Timesheets;
• Budgets; and
• Invoices.

Click here to learn more about Someka's Excel and Google Sheets templates