The example above is just a small set of the data per row. Note: the data is much longer that 255 characters per row. I changed the replace pattern to replace any sequence of one or more of carriage return or newline characters by a single space. So the result needs to be: 60 W 0 N J T 14790002 - Handels CRLF I need to remove any carriage return characters.-replace rn isnt working. The incorrect CRLF (line break) needs to be removed and the data that was on the next row needs to be concatenated. The string.Replace() method allows this, but we also need to identify what we want to replace with the html tag. I tried to use the RegEx tool (formula to remove any CRLF that is not followed by a digit and a semicolon: Replace: +^(+\s) ) but it doesn't concatenate the 2 rows back together again.ĭata looks like: 60 W 0 N J T 14790002 - Handels CRLF
Select the cell or cells, click Data/Text To Columns form Excels menu bar, choose the Delimited option on the first dialog page, click the. This will prevent two words combining in to one when you remove the carriage returns. However, I need to concatenate the (two or more) rows that had a false line break as well. Changing SUBSTITUTE (A1,CHAR (10),'') to SUBSTITUTE (A1,CHAR (10),' ') will replace all carriage returns with a space.
I have installed version 10.5.9 and used the Data Cleanser like in the screenshot above.
0 kommentar(er)
