Combining Text in Excel

Introduction

I just wrote a page on combining text and values in a cell in Excel and you can read that here

In this page, I am showing you how you might combine words from a list and sentences in a list, one by one, row by row. Like this:

Where do you want to place those words?

If you want to put them at the beginning or end of the sentence, use Flash Fill. That’s very easy to do and will give you your results in seconds.

If you want to use formulas

=B5&” “&A5

Assuming your sentence is in A5 and the word you want to put at the start of that sentence is in cell B5 … just fill down, again assuming that your words and sentences are in rows 6 and downwards

Alternative 1

To put the word at the end of the sentence

=A5&” “&B5

Again, fill down.

Alternative 2

If you want to put the word in the same place each time, in the middle of the sentence

=LEFT(A5,10)&” “&B5&” “&RIGHT(LEN(A5)-10)

Instead of typing 10 every time, you could put that in cell B3, for example, then change your formula to

=LEFT(A5,B$3)&” “&B5&” “&RIGHT(LEN(A5)-B$3)

The $ sign stops your formula change B3 to B4 then B5 etc as you fill it down.

Duncan Williamson

4th October 2022