MS Word Search advanced maybe with VBA

[03Explorer]

I can find all paragraph marks (as per MS WORD search definition) ^p. But I want the search to exclude two criterias.
Exclude either comma or Double Quote immediately before the paragraph mark.

Problem is the file is extremely too long to do manually and the macro option only gives me the choice to search with no exceptions/exclusions.

Am I approaching this issue from the wrong way?

Intent is to export database table to CSV file, then import into an another. Problem is csv file uses paragraph mark at the end of each record. Problem is a field that is [comment] also has paragraph marks which ruins the import.

We tried importing in the other location directly, instead of export to csv then import with no luck either.

So idea is:
1. export to csv
2. open with MS Word
3. run code against to remove paragraph mark that does not meet these requirements
a. comma before
b. double quotation mark before
4. save back as csv
5. perform import (hopefully with no errors)

Thanks

 

[mintjulep]

Brute force method, in multiple passes:

Search for ,^p and replace with some unique character or string for example '{~}'.

Search for "^p and replace with {~}.

Search for ^p and replace with nul.

Search for {~} and replace with ^p.

 

[03Explorer]

Mintjulep,

That is a clever idea. I will consider it. We have to see if we are fighting a battle with data as is, or have our data entry re-visit the problems in the database and then we have clean data the proper way. Thanks!

 

[TonyJollans]

Hi 03Explorer,

You shouldn't need VBA.

Find [blue][tt]([!^13"\,])^13[/tt][/blue] - that's left parenthesis, left (square) bracket, exclamation mark, caret, one, three, double quote, backslash, comma, right bracket, right parenthesis, caret, one, three

Replace [blue][tt]\1[/tt][/blue] - that's backslash, one

Check "Use Wildcards"

Hit Replace All

(if I've understood correctly)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[