Insert a carriage return for a new line within the same cell

[wendy_lu]

Hi, Alteryx expect,

I have a need to insert two carriage return for a long string into new lines when seeing  special characters, for example my current string is:"index1 is 10 and index2 is 20"
that I want to change to within the same field:

index 1 is 10

index2 is 20  

is there is functions to insert a carriage return with the Formula? or Regex has this feature? Thanks!

[Ned · Accepted solution]

Its one of those things that is not obvious because its so simple.  Just put the newline in quotes in the formula and it works.

[Field1] + "
" + [Field2]

will add the 2 fields with a newline in between.

[ChrisF · Accepted solution]

Hey Wendy, 

I wanted to try and add to Ned's advice.  His method will work great if you're trying to concatenate two fields into one and incorporate a carriage return, but if you don't have the data broken into two different fields already, you'll likely need to take a different approach.

Let's take your example: we've got a single field and the first row contains your string:

"index1 is 10 and index2 is 20"

If I understand your question correctly, you're looking to use a Formula to break the string up when it hits 'and', replacing the 'and' with a carriage return, but still keep it all in one cell.

My solution would be the following formula:

REGEX_REPLACE([string],' and ' , ' ' )

This formula will look through your string field for an instance of ' and ' and then replace it with two newlines.  The output ends up looking exactly like your example:



You could also expand this formula to look for a range of breakpoints by separating each term with a pipe.  For example, if you wanted to insert carriage return on either ' and ' or ' but ', you'd do this:

REGEX_REPLACE([string],' and | but ', ' ')

Hope that helps!

[wendy_lu]

Thank you, Ned and Cfreeman. Both worked great!

[lepome]

For whatever reason, when the field with the carriage return/newline is put into the body of an email using the Email Tool, the newline is ignored.  It's there if you look with a Browse, but isn't in the resulting email.  A workaround is to use the Report Text tool to create a field with the line break.  Then it shows up in the resulting email.

[Thorsty1]

Thanks!  Simple solve, but huge help!

[CalDowd98]

Hi Lisa,

I have a chunk of text that has several carriage returns included in the same cell. As you said before the report text tool will ignore these. How would i get these to show up on the report text tool, without having to split into mulitple columns ?

I cant split into mulitple columns, as each record is going to contain a different number of carriage return depending on what was done on the job !

Thanks !

[Ma3stro]

You're the best. Thank you for helping me with something that was a bit of a headache to a newbie!

[SeanReadFord]

Does anyone know a trick to make Excel  honor this?  I'm using a summarize tool to concatenate lines into one line, putting a separator of \n for the fields I'm concatenating, and it shows up properly split into lines on the binoculars/preview tool, but my last step is "output to excel", and Excel doesn't show the newline splits in the cell. 

[SeanReadFord]

Update - it *kind of* does.  Seems to be an Excel display issue.  The cell text appears all in one line when I open the sheet, but as soon as I put my cursor inside the cell, I can see it's split into multiple  lines, and when I move the cursor off that cell it displays in multiple lines.