• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Excel Tip – Insert The Contents Of A Cell Into A Text String – Formula Friday

Excel Tip – Insert The Contents Of A Cell Into A Text String – Formula Friday

March 9, 2018 by Barbara

Welcome back to another Excel #formulafriday blog. Today I want to share with you a nice handy Excel tip using a well-known formula. Let’s learn how to insert the contents of an Excel cell into a sentence. This is a really great way to save time if you use the same report every week or month. All you really need to do is update the report with the latest figures!.

Let’s walk through an example of my Monthly Sales Report. Every month I report on Sales Per Region, the standard format is the same every month and the only thing that changes is a handful of cells. Let’s free up some time and get this report a little more automated using the CONCATENATE Function.

Using The CONCATENATE Function.

The syntax of this formula is

=CONCATENATE(TEXT1,TEXT2…)

Up to 255 text entries can be added to the function and each one of them should be separated by a comma.

excel concatenate formula

One point to note, CONCATENATE does NOT add in extra spaces between text, in order to do this, you need to accommodate these extra spaces within the formula. Let’s take a look at an example.

Right, let’s get back to the extract from my report-

Excel Tip - Insert The Contents Of A Cell Into A Text String

Next, all I need to do is combine the text in cells B5:B8 with my monthly sales figures which are automatically updated on the datasheet of my Monthly Sales Report. You can be see these in the screenshot below. My sales figures I want to always refer to in the specified area of my Sales Report are contained in cells C6:C9.

As you have probably noticed we have some formatting on our cells that contain our sales figures for the previous month. So, I need to take into account this formatting for the sales report to not only look good but be easily interpreted.

Let’s get on with some formula fun.

First, we take CONCATENATE which simply combines two or more text strings into one. After that, we just need to separate the text strings with commas.

Building The Formula.

Let’s start with the Northern Region. We have three text strings as even space is classed as a separate text string. We also have to just ensure we force the formatting on the value sales from our data area of the Sale Report.

For the last argument (3) in the formula, we use the TEXT function, which takes two arguments. Firstly, it takes the Value (which we take from our data area of the sales report). We can then declare which formatting we want to use. I just need to hit enter to complete the process.

Now we have the formula set up. I just update the data area of my sales report. That’s all that I need to do. Excel automatically populates the presentation part of my Monthly Sales Report.

What Next? Want More Excel Tips?

 

If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

1

If you want to see all of the blog posts in the Formula Friday series. Click on the link below

How To Excel At Excel – Formula Friday Blog Posts.

More Excel Tips_New1
Learn Excel With Our Excel Online Courses

 

 

Filed Under: Blog, Excel Tips& Tricks, Formula Friday, Formulas Tagged With: Blog, Excel Formulas, formula friday

Comments

  1. Ordercustomessay says

    March 22, 2018 at 5:36 am

    how to use minus(-) in concatenate formula

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2023 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

Popular Pages

  • Home

More Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Excel Jobs

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

Search

Navigation

  • Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • Top Menu

Social

  • Twitter
  • Facebook
© howtoexcelatexcel.com. All rights reserved.
  • Privacy Policy