Replacing carriage returns in Excel with ~CR~~LF~ for importing data using Insight Studio

9/18/2009 6:36 PM
You can subscribe to this wiki article using an RSS feed reader.

Depending on if you want to preserve the the carriage returns (CR) in your data you can use one of the following methods.

1. Keeping the carriage returns

This method allows you to replace the values in place.

  • Edit>replace, in "Find What" hold down Alt and type 010, release Alt key, type ~CR~~LF~ in Replace With.

or if yout data contains another format it might need to look for 2 characters.

  •     Edit>replace, in "Find What" hold down Alt and type 010, release Alt key,Alt and type 013, release Alt key, type ~CR~~LF~ in Replace With.


Both of the above methods may not find all the carriage returns


2.  Using a formula to make a new column in your data with the CR replaced

  • Insert 2 new columns to the right of the data you are trying to correct                             
  • Insert the following formula in the first cell of your new column C1 where B1 is your souce column.
    • =SUBSTITUTE(SUBSTITUTE(B1,CHAR(13),"~CR~"),CHAR(10),"~LF~")                                   
  • Fill the formula down to all rows you are cleaning                                                  
  • Copy all of column C and using "Paste Special" pasete the "Values" into column D                    
  • Review the data in column D                                                                         
  • If it all looks good, delete columns B and C                                                       


3.  Removing all CR using a formula

  • Insert 2 new columns to the right of the data you are trying to correct
  • Insert the following formula in the first cell of your new column C1 where B1 is your souce column.
    • =CLEAN(B1)
  • Fill the formula down to all rows you are cleaning
  • Copy all of column C and using "Paste Special" pasete the "Values" into column D  
  • Review the data in column D
  • If it all looks good, delete columns B and C