How can I search/replace weird command in text imported from Summation database?

[Guest]

I am exporting data from Summation (legal database program) to a delimited text file, and then importing it into Excel to create a report. In fields where there is text separated by internal hard returns, the returns appear in Excel as little boxes. I can't figure out how to search/replace those commands with hard returns (or even a character like a semicolon) within those fields. Thanks in advance!

 

[David McRitchie]

Try turning on Cell wrap
Ctrl+A, Format, cells, alignment, wrap-

I am exporting data from Summation (legal database program) to a delimited text file, and then importing it into Excel to create a

report. In fields where there is text separated by internal hard returns, the returns appear in Excel as little boxes. I can't
figure out how to search/replace those commands with hard returns (or even a character like a semicolon) within those fields. Thanks
in advance!

 

[Guest]

Cell wrap doesn't do it. It's not a formatting problem, or problems with hard returns within the cell, it's a problem with these weird command codes. Thanks, though.

 

[David McRitchie]

Sorry, see this page for substituting the characters, preferably with
the TRIMALL macro or a modification of it. If they are not CHR(160)
which is the nonbreaking space you will have to add additional code.

Don't just remove the characters, change them to spaces then
with the EXCEL version of TRIM remove duplicate spaces and
trim the ends.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

You might want to try running the macro as it is first to see what
you have. Start on a copy of your worksheet.

Cell wrap doesn't do it. It's not a formatting problem, or problems with hard returns within the cell, it's a problem with these

weird command codes. Thanks, though.

 

[Jadeyez]

Which version of Summation are you using? In LG 2.5, Gold 2 and iBlaz
2.5, exporting a comma-seperated value (CSV) file creates quote mark
(") for each field entry--not hard return characters. Check the "Expor
Setup" menu. Summation forces you to use the quote character in orde
to export via CSV.

BEFORE you import into Excel, do a Search and Replace using Notepad t
remove the quotes. Save your changes. Make sure the saved text fil
still has the TXT file extension. Good Luck...

I work at a major law firm and use these two applications constantly
You should be able to resolve this within Summation before it gets t
Excel. Excel is excellent going the other direction--exporting the CS
file from an Excel worksheet to Summation using the "Import Summaries
feature.

Jadeyez
Summation Power User
Excel Novice Use