Replace Unknown Characters

  • I have a data file that is comma delimited (that I bring into excel). one section of the file contains several pices of information seperated by what appears to be a carriage return (shows up as a square box) I can use the find & repace to break it into seperate section. However I can not get rid of the carriage return.

    Does anyone know how I can get rid of it?

    I have tried the following formula without success:

  • Re: Removing Unwanted Returns

    Try using the CODE function to figure out what the character is, then replace that.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Removing Unwanted Returns

    Thanks for the info. The code function indicated that the characters were infact a char(13) and a char(10) (one of each). so I am not sure why the substitue function did not work. However I did get this to work:

  • Re: Removing Unwanted Returns


    The Carriage Return - Line Feed combination is used to signify new lines in text documents (at least using the default encoding of Excel-sourced csv files and Notepad-sourced txt files, for example).

    Because they are two characters together, did you try running a compound substitute on the cell such as:


    This should work. Optionally, if you don't mind getting rid of any other non-printing characters such as tab characters, then you should be able to use Clean:



  • Re: Removing Unwanted Returns

    Thanks. In this case the =clean(a1) would be fine.
    But I did get it to work with the substitute() as follows:


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!