Current I use "CONCATENATE" to join string in Excel, but I want insert character newlines and tab in string joined by "CONCATENATE".

Example:

• 
  

  I've string below when I completed join string.

  
  
  

  "Today is very beautiful"

  
  



• I expected string newlines and tab auto as below:

Today is

      verry

      beautiful

How to do that?

As TAB cannot be displayed in Excel cell, instead of using CHAR(9), in order to indent the line, I recommend using spaces. By using REPT() function, it will be easier to control the indent width by specifying the number of spaces (e.g. 4).

=CONCATENATE(A1, CHAR(10), REPT(" ", 4), A2)

Alternatively, I usually use & for concatenation to simplify the formula.

=A1 & CHAR(10) & REPT(" ", 4) & A2

Lastly, you have to format the cell as "Wrap text" by CTRL-1 > Alignment

You can try this formula:-

=CONCATENATE(A1,CHAR(10),CHAR(9),B1,CHAR(10),CHAR(9),C1)

Here

CHAR(10) - Line Feed/New Line

CHAR(9)  - Horizontal Tab

To see new line, you need to mark as checked on the Wrap Text box

Right click on Cell --> Format Cells --> Click on Alignment Tab --> Under Text control --> Check the box "Wrap Text"

Note: Tab spacing won't appear on the Cell

You could try adding newline and tabs yourself, as just another text element:

=CONCATENATE("Today is", Chr(13), Chr(9), "verry", Chr(13), Chr(9), "beautiful"

whereas Chr(13) produces a newline and Chr(9) a tab.

This post is old, but I wasn't able to find any satisfying answer to this question anywhere on Google, hence here's what I've come up with:

=CONCAT("Today is";CHAR(10);REPT(CHAR(1);2);"very";CHAR(10);REPT(CHAR(1);2);"beautiful")

will display as:

...which is more or less like this:

Today is

    very

    beautiful

CHAR(1) is the unprintable "Start of Heading" character in the ASCII system

Microsoft Office 365 ProPlus

You can also use "&" chars to concatenate text in Excel, as below:

The clause: =CONCATENATE(A1,CHAR(10),CHAR(9),B1,CHAR(10),CHAR(9),C1) could be written as:

 =A1&CHAR(10)&CHAR(9)&B1&CHAR(10)&CHAR(9)&C1

This form will be interesting when CONCATENATE overflows (it has a limit of entries).

There seems to be no way to make

="some string"&char(9)&"another string"

work. The resulting text pasted as plain text in a txt file or a as unformated text in word is as follows:

"some string another string"

Including the unwanted double quotes.
If pasted as keep or merged formatting it is as follows:

some string another string

Please note that the tab has been replaced with a single space.

If instead of char(9) a printable character such as “!” char(33) the resulting text pasted in word or a text editor is always:

some string!another string


The bottom line is:

Currently there appears to be no method to insert a tab in a cell. This is a pity as there is a genuine need for this. A workaround is to insert a character that isn’t expected in the cell and then use a post processer or a macro in the target editor. I intend to use a grave (just above the tab) and a word VBA macro to convert it to a tab.

Simple: Paste them from text editor as quoted plain text (TSV format).

No one mentioned this simple and obvious method so far.

1. 
   

   Create the cell content in Notepad with actual tab and newline characters. This example contains them all (here Tab is illustrated as ↹):

   
   
   

   Before Tab↹AfterTab
   
   Second "line" of the same cell.
   
   

   
   


2. 
   

   Surround the entire content with double quotes " (A conversion into TSV format).

   
   
   
   
   
   • 
     

     Should be there any double quotes already inside the original text, double them. For better clarity I put them into the above example.
     The above example will now look like:

     
     
     

     "Before Tab↹AfterTab
     
     Second ""line"" of the same cell."
     
     

     
     
   
   
   
   



3. Mark the entire text (Ctrl+A) and press Ctrl+C copy it into clipboard.



4. In Excel, navigate to the cell (but do not enter its edit mode) and press Ctrl+V to paste.

Done. Now the cell content is exactly as you expect it, including newlines and tabs. (You can verify it by formulas, VBA or by round trip, i.e. by copy-paste back to text file). Mandatory notices:

• You might need to increase row height to reveal the second line.




• Do not expect correct formatting of contained Tab characters. (But you know that already.) Excel is not designed to handle them properly inside the cell. Should you need left indentation of cell content, open Format Cells window (Ctrl+1) and set the Indent value on Alignment tab.

Tip: You can insert multiple cells by using newline and tab outside the quotes.

This example (two rows by two columns in TSV) will insert area of 2×2 cells at once:

A1↹B1

A2↹B2

If you see its point, you can add newlines and tabs inside the values:

"A↹1

cell"↹"B↹1

cell"

"A↹2

cell"↹"B↹2

cell"

Tested with Excel 2016.