Sage 100

have an excel file with customer number and notes we were able to extract from Business Works that I need to import into Sage 100. Problem is that the notes field has about 20 carriage returns per customer. Found a MS article about using different characters in Find/Replace (^13 or ^p is supposed to represent carriage returns) but it doesn't work. Saved as a standard text file and it puts the customer on one line, then each notes section on subsequent lines. Anyone know of an easy way to fix this file (get rid of returns) so I can import it? Don't really want to attach the file as there is a lot of customer info but could fudge up a few lines of it if it would help. Seems like a simple thing but I'm stumped.

Can you create a new column and use the REPLACE or SUBSTITUTE formula? =SUBSTITUTE(A1,CHAR(13), """") Otherwise I have found this article.. http://howtouseexcel.net/how-to-remove-line-breaks-and-carriage-returns-in-cells-in-excel

Interesting article @MichelleBennion - Thanks for posting. @EricAnderson Please let us know how this worked.

You could also create perform logic for VI with something like this in it VAR$=SUB(VAR$,$0D0A$,"""")

none of the excel articles work for me - the substitute formula didn't give me any errors but the carriage returns were still there. The other link says to get into find/replace and for Find enter Alt-010 (hold down alt key) which my computer just beeps at me and won't take it. The original article I found said to use ^13 for carriage return Find but that doesn't find any restults/matches. Michelle is going to take a closer look and we'll post any solution we find.

If you paste it into a text editor like notepad++ and use the ""show whitespace"" option you may be able to see exactly what the line break character is. Also, try a text to columns and enter ctrl-j as the delimiter and see if it splits it out by line breaks, and you can then concatenate it back together without the breaks.

I ended Up using the CLEAN() function... it gets rid of all 37 random (non-printable) characters that make our lives difficult.

CLEAN - of course, so simple. Definitely have to remember that one - thanks Michelle, the file looks great!

just imported - using excel file, worked like a charm, thanks again Michelle!

You bet! Glad I could help.

Great tip on the Clean function! Thank you for sharing that!