Concatenate Letter and Number: How to Combine Text in Excel
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
This article explains how to combine letters and numbers in a spreadsheet, typically using Excel or Google Sheets. You will learn the core functions, the correct syntax, and common mistakes to avoid, so you can merge text and numeric values into a single cell exactly as you need.
Formula
The most direct way to concatenate a letter and a number is using the ampersand (&) operator. In Excel or Google Sheets, the formula is:
=A1 & B1
Where:
- A1 contains the text (e.g., “ABC”)
- B1 contains the number (e.g., 123)
- The result is “ABC123”
You can also use the CONCAT function (replaces the older CONCATENATE):
=CONCAT(A1, B1)
Both methods produce identical results. The ampersand is often preferred because it is shorter and more flexible when adding extra text or formatting.
Worked Example
Using the default values from the calculator block:
- Text A = “ABC”
- Number B = 123
Formula: =A1 & B1
Step-by-step:
- Start with “ABC” (text)
- Add the ampersand:
& - Add the number:
123 - Excel combines them: “ABC” & 123 = “ABC123”
The result is the text string ABC123.
When to Use Concatenation
Concatenation is used whenever you need to merge data from different columns into one. Common scenarios include:
- Creating full names from first and last name columns
- Building email addresses from usernames and domains (e.g.,
=A1 & "@company.com") - Generating product codes from category letters and sequential numbers
- Combining address parts (street, city, ZIP) into a single cell
The result is always text, even if the original number was numeric. This is important because you cannot perform arithmetic on the combined value without converting it back.
How to Read the Result
The output is a text string. If you concatenate a number, the number’s formatting is lost. For example, if B1 contains 1234.5 and you concatenate, you get “ABC1234.5” – not “ABC1,234.5”. If you need to preserve formatting (like currency symbols or thousand separators), use the TEXT function:
=A1 & TEXT(B1, "0.00") gives “ABC1234.50”
=A1 & TEXT(B1, "#,##0") gives “ABC1,234”
Similarly, if you concatenate a date, Excel converts it to its serial number (e.g., 45000). To keep a readable date, use TEXT(B1, "mm/dd/yyyy").
Common Mistakes and How to Avoid Them
- Forgetting the ampersand: Using a comma instead of & will cause an error. Always use & between values.
- Not adding spaces or separators: If you want “ABC 123” instead of “ABC123”, include a space in quotes:
=A1 & " " & B1. - Numbers stored as text: If a number is already text (e.g., imported from a CSV with leading zeros), concatenation works fine, but you cannot use it in calculations without converting.
- Using CONCATENATE with a range: The old CONCATENATE function does not accept a range like A1:A5. Use CONCAT or TEXTJOIN for ranges.
- Ignoring the TEXT function for dates/currencies: Without TEXT, dates become serial numbers and currencies lose their symbols.
Alternatives: TEXTJOIN and Flash Fill
For combining multiple cells, TEXTJOIN is more powerful. It lets you specify a delimiter and ignore empty cells:
=TEXTJOIN(" ", TRUE, A1:A10) joins all non-empty cells in A1:A10 with spaces.
Flash Fill (Excel 2013+) is a non-formula alternative. Type the desired result in the first cell, then press Ctrl+E to auto-fill the pattern. It works well for simple concatenations but is not dynamic – if the source data changes, you must redo it.
Frequently Asked Questions
How do I concatenate a letter and a number without losing the number’s formatting?
Use the TEXT function to format the number before concatenating. For example, =A1 & TEXT(B1, "0.00") keeps two decimal places. For dates, use TEXT(B1, "yyyy-mm-dd").
Can I concatenate a number and keep it as a number?
No. Concatenation always produces text. If you need the result to be numeric (e.g., “123” from 1 and 23), you can multiply by 1 or use VALUE: =VALUE(A1 & B1) – but this only works if the combined string is a valid number.
What is the difference between CONCAT and CONCATENATE?
CONCAT is the modern replacement (Excel 2016+ and Google Sheets). It supports ranges, while CONCATENATE does not. Both work the same for simple cell references.
How do I add a separator like a dash or space?
Include the separator in quotes: =A1 & "-" & B1 gives “ABC-123”. For a space, use " ".
Why does my concatenated date show a number like 45000?
Dates are stored as serial numbers in Excel. To display a readable date, wrap the date cell with TEXT: =A1 & TEXT(B1, "mm/dd/yyyy").
Related
Formula used
in a | Text A | - | ABC | 0 in b | Number B | - | 123 | 0 out result | Combined text | - | a & b | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.