split a text into several lines (inside a cell) in Excel using VBA

0
votes

I'm using Excel to generate a summery report for my data. I want each summery to be displayed in a single cell. I used VBA and I got something like this:

enter image description here

I want to be able to display the data like this:

enter image description here

All the lines should start with "Increase" or "Decrease". How can I do it in VBA? (I have many cells like the one in this example) how to go over a text inside a specific cell and add a new line after each of these words?

1
vbaexcel

1 Answers

3
votes

If you are trying to fix the macro so it formats the lines correctly in future then you you need to add in a line feed (I use CrLf myself but Excel, for cells, only cares about the Line Feed) by concatenating the elements with vbLf.

My thanks to @retailcoder for pointing out that vbNewLine == vbCrLf, so you can use whichever makes more sense to you.

so something like Range.Value = "line 1" & vbcrlf & "line 2" & vbcrlf & "line 3" etc:

Range.Value = _
    "line 1" & vbcrlf & _
    "line 2" & vbcrlf & _
    "line 3"

or, personally, I prefer to construct the text through a loop or using Join():

Range.Value = Join(Array( _
    "Line 1", _
    "Line 2", _
    "Line 3"), vbCrLf)

For it to actually display on new lines, you need to double check that Word Wrap is on (Excel will set it on automatically but just in case it has been overridden by something else) using Range.WrapText = True

However, I realise an interpretation is that you just want to quickly fix what is already on the sheet. If this is the case then just manually do a Find and Replace of the word "Increase" or "Decrease" with Char(10)Increase or Char(10)Decrease respectively, where Char(10) is what you can get by pressing Alt+Numpad 0010, or by pasting in the value of the result from the formula =CHAR(10). Then remove the first character using a formula.