Function Problems

How to Combine Excel Data with Fixed Text into a Custom Format

Ayan Masood Oct 12, 2026 869 views

Question details

The user wants to combine data from an Excel workbook, such as phone numbers, with specific fixed text before and after the value to create a customized string format.

How to Combine Excel Data with Fixed Text into a Custom Format

Product: Excel
Device & OS: not provided
Scenario: Formatting data by appending and prepending fixed text to cell values for data migration, exports, or specific system imports.
Observed behavior: Needs a reliable formula to merge fixed text strings with existing cell references across workbooks without displaying the formula itself.

Before you start

Ensure both the source and destination workbooks are open if you are referencing cells across different files, and take note of the exact sheet name containing your source data.

Solution 1Recommended

Use the Ampersand (&) Operator to Concatenate Text

The most straightforward method to combine fixed text strings with cell values is by using the ampersand (&) symbol in an Excel formula.

The ampersand operator (&) allows you to join multiple text strings and cell values into a single text item. By enclosing fixed text in double quotes, you can easily sandwich dynamic cell references between static words or punctuation.

Select the destination cell

Click on the cell in your destination workbook where you want the newly combined text to appear.

Enter the combination formula

Type the formula using double quotes for fixed text and ampersands to join them with the cell reference. For example: ="Phone,Default,"&'Old Spreadsheet'!J1&",True".

Fill the formula down

Click and drag the small square fill handle at the bottom-right corner of the selected cell to apply the formula to the rest of the column.

Turn off Show Formulas

If you see the literal formula text instead of the combined result, navigate to the Formulas tab on the ribbon and click 'Show Formulas' to toggle it off.

Use the Ampersand (&) Operator to Concatenate Text

Cross-Workbook References: When referencing another workbook, ensure the file name and sheet name are correctly spelled. If the sheet name contains spaces, it will automatically be enclosed in single quotes.

Manage Data Efficiently with WPS Office

Use WPS Spreadsheet to Combine Text and Data

WPS Spreadsheet provides powerful string manipulation functions and seamless formula support to help you merge, format, and organize your data effortlessly across multiple workbooks.

1. Open your workbooks in WPS: Launch WPS Spreadsheet and open both the source workbook containing your raw data and the destination workbook where you want the formatted text.
2. Type the concatenation formula: In the destination cell, enter your formula using the ampersand operator, such as ="Phone,Default,"&[SourceBook]Sheet1!J1&",True".
3. Apply to the entire dataset: Double-click the fill handle in the lower-right corner of the active cell to instantly fill the concatenation formula down your entire data column.

Fully compatible with Microsoft Excel formulas and cross-workbook referencing.Advanced text functions like CONCATENATE and TEXTJOIN readily available.Lightweight software with an intuitive, user-friendly interface for faster data management.

Frequently Asked Questions

Why is my cell displaying the formula instead of the combined text?

This usually happens because the 'Show Formulas' feature is enabled. To fix this, navigate to the Formulas tab and click 'Show Formulas' to toggle it off. Alternatively, check if your destination cell is formatted as 'Text' instead of 'General' before typing the formula.

Can I use a specific function instead of the ampersand symbol?

Yes, you can use the CONCATENATE or CONCAT function to achieve the exact same result. For example, typing =CONCATENATE("Phone,Default,", 'Old Spreadsheet'!J1, ",True") works identically to the ampersand method.

How do I add a space between the fixed text and the cell value?

To include spaces in your final result, you must insert a space inside the double quotation marks of your fixed text string. For example: ="Phone, Default, " & J1 & " , True" will add spaces around the cell value.

Excel & Data Problems