Last year, I showed that you could combine text in Excel by using the ampersand (&) operator, instead of the CONCATENATE function. That makes it much easier for those of us who are lazy, or can’t remember how to spell concatenate.
There was a combine text video too, and I’ve it added to the bottom of this post, so you can see it again. It’s more interesting than most of the new programs that are starting on television this season!
Formula is Showing
After watching that video on YouTube, someone asked why his worksheet was showing those combine text formulas, instead of the results.
There are two likely causes for this, described below. And the good news is that it’s easy to fix both!
Excel has a feature that lets you switch between seeing the formulas, and formula results, by using the keyboard shortcut: Ctrl +’
That ‘ character is the grave accent, and it’s at the top left on my keyboard, just above the Tab key .
Or, use the Ribbon command to make the switch – on the Formula tab, click Show Formulas
If the problem isn’t fixed by clicking the Show Formulas button, then the cell formatting is causing the problem.
To fix the formatting:
- Select the cells where formulas are showing
- On the Excel Ribbon, click the Home tab
- In the Number group, change the cell format from Text, to General
With the cells still selected,
- Click the Home tab on the Ribbon
- Click Find & Select, and click Replace
- In the Find What box, type an equal sign: =
- In the Replace With box, type an equal sign: =
- Click Replace All, then click OK, and close the dialog box.
Combine Text Without Concatenate Video
Or watch it on YouTube: Combine Cell Data in Excel Without Concatenate