Removing Carriage Returns

[lwilt]

I'm trying to remove all of the carriage returns in excel but keep running into a problem where some still seem to exist. I've tried:

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

=SUBSTITUTE(SUBSTITUTE(A1,CHAR(13),""),"<br>","")

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

But I am still getting some carriage returns. Any ideas on how to remove them all?

thanks

 

[lohithsriram]

Hi Lwilt,

Would you upload the file to check the issue. That should help you to get the quick responses. Thanks.

 

[Luke M]

If substituting for CHAR(10) is not removing them, it may be helpful to use the CODE function to determine what character it is exactly that you have in the string.

 

[Marc L]

​

Hi lwilt,​

please, please, please, ♪♫ (like the song !) just have a try with this formula : =CLEAN(A1) !​

Like it ?​

 

[bobhc]

Good day IwiIt

You have used the formulas but still getting some, have you tried Marc L's hint, a bit musical but I think he is tone deaf, or the trim function for spaces, this link goes through your problem but it dies not give any extra help other than sub links.

An upload as per Lohith's suggestion would help.


.

 

[shrivallabha]

What happens if you try following technique:
1. Select the range where you need this replaced.
2. Invoke Find and Replace [CTRL+H]
3. Stay in Find and hit CTRL+J
4. Keep Replace blank as it is and then hit replace all.

 

[SirJB7]

Hi, lwilt!
Give a look at the uploaded file. There you'll notice that either substituting CHAR(10) or CHAR(13) works: check before and after lengths (col. B & E).
So if you can't handle to manage SUBSTITUTE function work for a given set of data, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!