• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 143
  • Last Modified:

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
0
sunhux
Asked:
sunhux
  • 5
  • 4
  • 2
  • +2
6 Solutions
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 5
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now