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

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!

Preparation

Accepted answers

Ned

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

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:

rtaImage (4).png

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!

All comments

Ned

ChrisF

wendy_lu

Thank you, Ned and Cfreeman. Both worked great!

LisaL

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!

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.

ColinG

Unfortunately if you are using a report text tool and a render tool to write to PDF then Ned and jeblucas suggestions do not work.

Lepome's suggestion might work but in my situation would be a silly number of tools to join everything back together because I have some logic in mine (i.e.)

If !IsEmpty([Field 2]) then
[Field 1] + (carriage return that doesn't work in Alteryx) + [Field 2] else
[Field 1] endif

Alteryx many vagaraties frequently become exhausting. This should be easy to do or find the answer in 5 minutes. I've been at it almost 2 hours. I'm about to have to restructure my whole flow to account for the fact I can't figure it out right now, and that will take several additional hours.

ColinG

Actually Lepome's suggestion didn't work either. When I created the concatenated field with the carriage return and line break in a report text tool, and then later used that field in a 2nd report text tool where I really need it to work, instead I got the below output visible in the PDF.

This forum software is a little too smart for it's own good. I couldn't paste the text directly in or it would say not allowed. I couldn't find a way to encapsulate/escape it to just display. I couldn't show a screenshot of the actual PDF due to needing to redact the name and title of the person I was using.

tgarf12

Hi Ned,

Is this in the regular formula tool or the multifield formula tool?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703