Excel/Word 2003 macros to replace variable text strings with null/space

www.aa.com.crt_12345_1.txt
www.bb.com.crt_678_2.txt
www.cc.com.crt_99_3.txt
.....

I would like to put all the above hundreds of lines in Word/Excel &
remove everything from .crt_XXX_Y.

So the resulting output should be:
www.aa.com.txt
www.bb.com.txt
www.cc.com.txt

I'm not too good with Excel/Word, so pls give a very layman's step by step instructions.
I've read about creation subFunction etc but don't even know how to invoke them
sunhuxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
We cannot open the links. You need to post them as attached files not as a link.
gowflow
0
sunhuxAuthor Commented:
In the days I was administering OpenVMS, there's a TPU editor which I
could define macros to search for the string ".com.", start the "select",
then search for the ".txt" string, then invoke the "cut" function & record
these repeated keys in a macro by say pressing F12 to represent the series
of keys being pressed.

Then I would create another TPU macro which will record say pressing ten
times of F12 & record it to another key, say F11 & just by pressing F11,
I could do ten replacements such strings
0
gowflowCommented:
Leave the programing for us will arrange that simply post the files properly as stated above.
gowlfow
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sunhuxAuthor Commented:
Those are not links that I've posted but only strings of plain text.

I'll also need to amend the text below to :
www.aa.com.crt_12345_1
www.bb.com.crt_678_2
www.cc.com.crt_99_3
. . .

www.aa.com.crt_12345_1
www.bb.com.crt_678_2
www.cc.com.crt_99_33

So will another macro that searches for ".crt", start "Select", then press the "End"
key to go end of the line, then press "Cut" or "Del" key
0
sunhuxAuthor Commented:
Just treat those underlined links as plain text: somehow EE translates
them to links but I was just entering plain text
0
GrahamSkanRetiredCommented:
In Word you can use a Wildcard Find and Replace.

Open the Find/Replace with Ctrl+H

In the 'Find what' box type : *13
Note that this line starts with a space.

In the 'Replace with' box type : *13

Click on the More>> button and tick the 'Use Wildcards' checkbox and the click on the 'Replace All' button
0
Saqib Husain, SyedEngineerCommented:
In excel, you do not need a macro.

Press ctrl-H
In "Find what" type "crt*.txt" without the quotes
in "Replace with" type "txt" again without quotes
Click replace all
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamSkanRetiredCommented:
Misread the brief:
In Word the Find and Replace are similar to the Excel solution that Saqib Husain, Syed has posted.
The Find can be the same, but the Replace should be: .txt^13
0
sunhuxAuthor Commented:
In Excel 2003, there's no "More>> button and tick the 'Use Wildcards' checkbox"
but there is "Options" button which does not show any 'Use WIldcards'.

What Saqib gave works.   Just curious, if we were to treat * as a character (not as
a wildcard) ie if we want to replace * with say null, how do we go about replacing?
Not that I have this need but curious
0
GrahamSkanRetiredCommented:
In Excel, the escape character is a tilde: ~
Precede the wildcard characters * or ? with a tilde if you are actually trying to find one of them.
0
sunhuxAuthor Commented:
One last question:
if I'm replacing <tab> character with a space character, how does this go?
0
Saqib Husain, SyedEngineerCommented:
The easiest way is to copy the tab character from the data and paste it in the search/replace window
0
GrahamSkanRetiredCommented:
In Word Find, the tab character is ^t
0
KromptonCommented:
You could also look into regular expressions (RegEx) for VBA. It is not as easy as using find and replace but it is an alternative.

Krompton
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.