14

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?

  • 1
    Not a solution for Excel users, but it might help someone: The spreadsheet program "Gnumeric" accepts tabs. – Nicolas Raoul Sep 2 '16 at 7:54
18

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

9

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

  • Here's a link to the Excel help for the CHAR function. Here's the table of values that you can use on Windows and on Mac via Wikipedia. It is important to note that the results may be different on each OS. – Elliott Jun 11 '17 at 17:23
2

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.

  • Sorry Boy's Rules, Char(13) is a carriage return, not new line. My bad – boretom Mar 28 '12 at 19:10
1

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:

enter image description here

...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

0

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).

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.