Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

wendy_lu
6 - Meteoroid
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!
10 REPLIES 10
Ned
Founder
Founder
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
Alteryx Alumni (Retired)

 

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!



 

wendy_lu
6 - Meteoroid
Thank you, Ned and Cfreeman. Both worked great!
LisaL
Alteryx
Alteryx

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.

Image.png

Lisa LePome
Senior Customer Support Engineer -- Knowledge Management Coach
Alteryx, Inc.
Thorsty1
7 - Meteor

Thanks!  Simple solve, but huge help!

CalDowd98
8 - Asteroid

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
5 - Atom

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

SeanReadFord
6 - Meteoroid

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
6 - Meteoroid

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.

Labels