Concatenated data in a text Box

[SteveT57]

Hi I Have a text box on a graph with Concatenated data and I would like to be able to force different parts of the data onto a new row. Below is my formulae:
="Current Week Total - "&L6&""&" Last 13 Weeks Avg - "&ROUND(L7,0)&" % Chg "&TEXT(M6,"0.0%")
I would like new rows as per below

="Current Week Total - "&L6&""&" - 1st row
Last 13 Weeks Avg - "&ROUND(L7,0)&" - 2nd row
% Chg "&TEXT(M6,"0.0%") - 3rd row

Thanks in advance

Steve.T

 

[Rick Rothstein]

Just concatenate CHAR(10) into your formula wherever you want the line break to occur. For your request...

="Current Week Total - "&L6&""&CHAR(10)&"Last 13 Weeks Avg - "&ROUND(L7,0)&CHAR(10)&"% Chg "&TEXT(M6,"0.0%")

Note that you won't see the individual lines in the cell itself until you format the cell for "Word text".

 

[Roli001]

Hi I Have a text box on a graph with Concatenated data and I would like to be able to force different parts of the data onto a new row. Below is my formulae:
="Current Week Total - "&L6&""&" Last 13 Weeks Avg - "&ROUND(L7,0)&" % Chg "&TEXT(M6,"0.0%")
I would like new rows as per below

="Current Week Total - "&L6&""&" - 1st row
Last 13 Weeks Avg - "&ROUND(L7,0)&" - 2nd row
% Chg "&TEXT(M6,"0.0%") - 3rd row

Thanks in advance

Steve.T

Try using CHR$(13) as a carriage return

MsgBox ("First Line " & Chr$(13) & _
     "Second Line ")

 

[SteveT57]

Thanks Roli001,
It works sort of as gget the data on a new row but end with Chr$(13) as part of the text

 

[Rick Rothstein]

Thanks Roli001,
It works sort of as gget the data on a new row but end with Chr$(13) as part of the text

If you are doing this in VBA (which I did not get from your postings), try using Chr$(10) instead or, better yet, use the built-in VB constant vbLf instead. So...

'Either this...
 
MsgBox "First Line " & Chr$(10) & "Second Line "
 
'or this...
 
MsgBox "First Line " & vbLf & "Second Line "

 

[SteveT57]

Thanks Roli001,
It is all working now

 

[Roli001]

Great to hear. Thanks for the feedback.