Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer
    
Thanks boretom. Char(13) ^~^ –  Boy's Rules Mar 28 '12 at 17:17
    
Sorry Boy's Rules, Char(13) is a carriage return, not new line. My bad –  boretom Mar 28 '12 at 19:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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