Function Problems

How to Add Text Between Transposed Text in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user needs to insert specific text characters or strings between data that has been transposed in Excel, which requires combining transposition tools with text manipulation formulas.

Product: Excel
Device & OS: not provided
Scenario: Formatting and manipulating data after transposing it from rows to columns (or vice versa) to create a single readable string or custom formatted layout.
Observed behavior: The user wants to combine transposed elements with additional text separators, but needs the correct formula or Power Query setup based on their specific data layout to achieve the desired output.

Before you start

Before applying formulas, ensure you know your source data range and the exact text (such as a comma, space, or specific word) you want to insert between the transposed values.

Solution 1Recommended

Use TEXTJOIN with the TRANSPOSE Function

The most efficient way to add text between transposed data is by nesting the TRANSPOSE function inside a TEXTJOIN formula.

TEXTJOIN allows you to specify a delimiter (your inserted text) and combine an array of values. By nesting TRANSPOSE inside it, you convert the column layout to a row (or vice versa) while simultaneously inserting your text.

Select the destination cell

Click on the empty cell where you want the transposed and combined text to be displayed.

Enter the TEXTJOIN formula

Type =TEXTJOIN(" your text ", TRUE, TRANSPOSE(A1:A5)) into the formula bar. Replace 'A1:A5' with your actual data range, and replace ' your text ' with the text you wish to insert.

Execute the formula

Press Enter. The transposed data will appear as a single text string with your custom text inserted between each original cell value.

Dynamic Array Support: If you are using an older version of Excel that does not support dynamic arrays natively, you may need to press Ctrl+Shift+Enter to evaluate this correctly as an array formula.

Solution 2

Use Power Query to Transpose and Merge

For larger datasets or recurring data processing tasks, Power Query offers a visual, robust method to transpose data and merge columns with a custom text separator.

Efficient Data Manipulation

Combine and Transpose Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced text functions like TEXTJOIN and TRANSPOSE, along with dynamic arrays, allowing you to manipulate your data quickly and efficiently without compatibility issues.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the source data you want to transpose.
2. Apply the nested formula: Select your target cell and input =TEXTJOIN(" custom text ", TRUE, TRANSPOSE(your_range)).
3. Press Enter to complete: Hit Enter to instantly calculate the formula and display your transposed data with the added text in between.

100% compatible with Microsoft Excel formulas (.xlsx)Natively supports TEXTJOIN, TRANSPOSE, and dynamic array calculationsFree, lightweight, and fast to process large datasetsFamiliar ribbon interface ensures a zero-learning-curve transition

microsoft office alternative - wps office

Frequently Asked Questions

Can I add different text strings between each transposed cell?

Yes, but TEXTJOIN uses a single uniform delimiter. To insert different text between specific cells, you will need to use multiple nested CONCATENATE functions or use the '&' operator to manually construct the string (e.g., =A1 & " text1 " & A2 & " text2 ").

Why does my TRANSPOSE formula return a #VALUE! error?

This error typically occurs in older spreadsheet versions if you press just Enter instead of Ctrl+Shift+Enter, which is required to process the formula as an array. It can also happen if the source range itself contains #VALUE! errors.

Does the TEXTJOIN method ignore empty cells in my source data?

Yes, if you set the second argument of the TEXTJOIN function to TRUE, it will automatically ignore empty cells in the transposed range, preventing unwanted duplicate text insertions.

Excel & Data Problems