This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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!
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:
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.
Lisa LePome Principal Support Engineer -- Knowledge Management Coach Alteryx, Inc.
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 !
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.
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.