"Embedded new line" message in cell containing numbers

Question

Hello, i'm new to Alteryx. So i have a column that includes whole numbers as well as decimals. Here is a sample

2.5

40.5

3

23.5

2

1

The data type right now is a V_string but I will change in to a fixed decimal because we will need to perform a sum on the values later on.

For some of the numbers  that appear in the column, I have the message "This cell has an embedded new line". What does that mean and how do I eliminate the message.

Thanks

Thableaus · Accepted Answer

Hi @irmb

This means you have the new line character (
) present within your cells.

To get rid of them, you can use this RegEX.

REGEX_Replace([Field], "
", "")

Cheers,

JoeS · Accepted Answer

As above, this means that there is a new line character - for example someone has pressed alt + enter in the cell in Excel to add a new line.

The above will work, also our Data Cleanse tool within the preparation section has the check box to remove embedded new lines as well.

danilang · Accepted Answer

Hi @irmb

Both @JoeS and @Thableaus are correct that the new line character(
) is embedded in the cell.  However, if you simply remove it, you'll end up with the values concatenated in the same row, i.e  "2.540.5323.521".

You'll to use either the Text to Columns tool or the Regex tool in tokenize mode to split the field on "
" into separate rows

Dan

danilang · Accepted Answer

Hi @irmb

Notice the second message in your screen shot.  It has "23.524.5" in it.  This is the situation I referred to in my post.  When you simply remove the new lines, the number 23.5  was concatenated with the numbers after it giving an invalid number.  You need to split to rows using 
 as the delimiter.  That will ensure that each of your values is on one row and can then be converted

Dan