Contents
The inscription on the fence: “Katya + Misha + Semyon + Yura + Dmitry Vasilyevich +
comrade Nikitin + red-haired plumber + Vitenka + telemaster Zhora +
bastard Redulov + can’t remember his name, he’s so long-haired +
19 more men + husband = love!”
Method 1: CONCATENATE, CONNECT, and JOIN functions
In category Text there is a function STsEPIT (CONCATENATE), which combines the contents of multiple cells (up to 255) into one unit, allowing them to be combined with free text. For example, like this:
Nuance: do not forget about the spaces between words – they must be written as separate arguments and enclosed in brackets, because the text.
Obviously, if you need to collect a lot of fragments, then using this function is no longer very convenient, because. you will have to write links to each cell-fragment separately. Therefore, starting from the 2016 version of Excel, to replace the function STsEPIT came its more advanced version with a similar name and the same syntax – the function STEP (CONCAT). Its fundamental difference is that now you can specify not single cells, but entire ranges as arguments – the text from all cells of all ranges will be merged into one whole:
For bulk join it is also convenient to use the new function COMBINE (TEXTJOIN), introduced since Excel 2016. It has the following syntax:
=JOIN(Separator; Skip_blank_cells; Range 1; Range 2 …)
where
- Separator – the symbol to be inserted between the fragments
- The second argument controls whether empty cells should be ignored (TRUE or FALSE)
- Range 1, 2, 3 … – ranges of cells whose contents we want to glue
For example:
Method 2. Symbol for gluing text (&)
This is a universal and compact method of chaining that works in absolutely all versions of Excel.
For summation the contents of multiple cells use the plus sign “+“, and for gluing cell contents use the sign “&” (located on the number “7” on most keyboards). When using it, remember that:
- This symbol must be placed at each connection point, i.e. at all “junctions” of text lines, just as you put several pluses when adding several numbers (2 + 8 + 6 + 4 + 8)
- If you need to paste arbitrary text (even if it’s just a period or a space, not to mention the whole word), then this text must be enclosed in quotation marks. In the previous example with the CONCATENATE function, Excel itself takes care of the quotes – in this case, they must be set manually.
Here, for example, is how you can collect a full name in one cell out of three with spaces added:
If you combine this with the function of extracting the first letters from the text – LEVSIMV (LEFT), then you can get a surname with initials with one formula:
Method 3. Macro to merge cells without losing text.
We have text in several cells and the desire is to merge these cells into one, merging their text into the same place. The only problem is the button. Combine and place in the center (Merge and Center) in Excel, it knows how to merge cells, but with text it’s difficult – only the text from the top left cell remains alive.
To merge cells with text merge (as in Word tables), you will have to use a macro. To do this, open the Visual Basic Editor on the tab Developer – Visual Basic (Developer — Visual Basic) or keyboard shortcut Alt+F11, insert a new program module into our book (menu Insert – Module) and copy the text of such a simple macro there:
Sub MergeToOneCell() Const sDELIM As String = " " 'separator character Dim rCell As Range Dim sMergeStr As String If TypeName(Selection) <> "Range" Then Exit Sub 'If no cells are selected, exit With Selection For Each rCell In . Cells sMergeStr = sMergeStr & sDELIM & rCell.Text 'gather text from cells Next rCell Application.DisplayAlerts = False 'turn off standard text loss warning .Merge Across:=False 'merge cells Application.DisplayAlerts = True .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM)) 'add summary text to merging cell End With End Sub
Now if you select multiple cells and run this macro with the keyboard shortcut Alt+F8 or by button Macros on the Developer tab (Developer — Macros), then Excel will merge the selected cells into one, merging the text into the same place through spaces.
- Divide the text into chunks
- Merge Multiple Cells Into One While Keeping Text Using PLEX Add-on
- What are macros, how to use them, where to insert macro code in VBA