Mastering Excel Functionality: How To Insert Text Data Into A Cell Via Formula

Quick Links

• Add Text to the Beginning of a Cell
• Add Text to the End of a Cell
• Add Text After a Specific Number of Characters
• Add Text After a Specific Character

Key Takeaways

To add text to the beginning or the end of your existing text, use the “&” operator or the CONCAT function. Using other functions, you can add text at the nth character or before or after a specific character.

Do you want to add some text to the beginning, middle, or end of the existing text in your cells? Microsoft Excel has you covered. You can use a formula or function to append text to your cells, including finding a specific character and adding your text before or after it. Here’s how.

Add Text to the Beginning of a Cell

To add some text before your existing text in a cell, simply use the & (ampersand) operator or the CONCAT function. Both work the same way.

To use them, first, open your Excel spreadsheet and select the cell where you want to display your merged text.

Click here

In the chosen cell, type the following formula and press Enter. In this formula, replace `Mr. ` (note the space after the text) with the text you want to add and `B2` with the reference of the cell where you want to append your text.

=”Mr. “&B2

Note that we’ve enclosed the text to add in double-quotes. You can add any text, spaces, numbers, or symbols within these quotes, and Excel will append them to your existing cell content.

If you’d like to use the CONCAT function , then in the cell where you want to display the result, enter the following function and press Enter. Here, replace Mr. with the text to add and B2 with the cell reference where your existing text is.

=CONCAT(“Mr. “,C2)

Your result will look like the following:

Related: How to Add Space Between Text and Cell Borders in Excel

Add Text to the End of a Cell

To append your custom text at the end of your existing text, use the same & (ampersand) operator or the CONCAT function.

If you’d like to use the & operator, then in your Excel spreadsheet, click the cell where you want to display the result.

Screensaver Factory, Create stunning professional screensavers within minutes. Create screensavers for yourself, for marketing or unlimited royalty-free commercial distribution. Make screensavers from images, video and swf flash, add background music and smooth sprite and transition effects. Screensaver Factory is very easy to use, and it enables you to make self-installing screensaver files and CDs for easy setup and distribution. Screensaver Factory is the most advanced software of its kind.

In the selected cell, type the following formula and press Enter. Here, replace B2 with the cell where your existing text is and (Journalist)with the text you want to append.

=B2&” (Journalist)”

There’s a space before (Journalist) so that this custom text adds a space right after your existing text ends.

PCDJ Karaoki is the complete professional karaoke software designed for KJs and karaoke venues. Karaoki includes an advanced automatic singer rotation list with singer history, key control, news ticker, next singers screen, a song book exporter and printer, a jukebox background music player and many other features designed so you can host karaoke shows faster and easier!
PCDJ Karaoki (WINDOWS ONLY Professional Karaoke Software - 3 Activations)

If you’d like to use the CONCAT function, then in your chosen cell, enter the following and press the Enter key. Once again, replace B2 with the cell containing your existing text and (Journalist) with the text you want to add.

=CONCAT(B2,” (Journalist)”)

And here’s what the result will look like:

Related: How to Add Alternative Text to an Object in Microsoft Excel

Add Text After a Specific Number of Characters

Excel allows you to add custom text after the specified number of characters in your existing text or after a specific character.

To add your text after a specific number of characters, use the LEFT , RIGHT , and LEN functions in combination with the & operator.

For example, to add a - (hyphen) after the second character in the C2 cell’s content, select the cell where you want to display the result.

In the selected cell, type the following formula and press Enter. Here, replace all instances of C2 with your cell reference, - (hyphen) with the text you want to add, and 2 with the number of characters after which you want your custom text to appear.

=LEFT(C2,2)&”-“&RIGHT(C2,LEN(C2)-2)

Soon as you press Enter, Excel will add the specified text and display the result.

Add Text After a Specific Character

In case you want to add text after or before a specific character in a cell’s text content, then use Excel’s LEFT , SEARCH , RIGHT , and LEN functions.

For instance, to add 212 (NYC’s area code) after the # (hash) symbol, you’ll use the following formula. Here, you’ll replace C2 with the cell where your existing text is, # with the character after which you want to append your text, and 212 with the text to add.

=LEFT(C2,SEARCH(“#”,C2))&”212”&RIGHT(C2,LEN(C2)-SEARCH(“#”,C2))

You’ll see the result in your selected cell.

Once you’ve used a formula or function from the above sections, you can automatically copy it for all your records in the sheet. To do that, from the bottom-right corner of the cell where you’ve entered your function or formula, drag downwards until all your records are covered. And that’s all.

Is your concatenated text not fitting Excel’s default cell size? There’s a way to shrink or expand your cells .