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:

    Code
    =substitute(a1,CHAR(10),"|") 


=substitute(a1,CHAR(13),"|")

    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:


    Code
    =LEFT(A3,FIND(CHAR(13),A3,1)-1)

    Re: Removing Unwanted Returns


    Hi


    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:


    =SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),"|"),CHAR(10),"|")


    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:


    =CLEAN(A1)


    Richard

    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:


    =substitute(a1,char(13)&char(10),"",1)

Participate now!

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

Register Yourself Login