Discussion on:

Quickly replace multiple space characters with a tab character

This has to be one of the most helpful tidbits of knowledge I've found on TechRepublic so far!

I work with an enterprise tape backup software that originated in Unix and was ported to Windows (poorly, I might add). The company that bought out this software ( EMC) didn't do much to update it and, hence, all the administration has to be done via the command line. Well, all the reports that you get from the command line are text based and, not surprisingly, the format is delimited by a random number of spaces!

I tried this method out on one of my reports and voila!, it worked perfectly! No longer do I have to bend over backwards to make my data readable and usable! Thank you Susan Harkins, you are an IT Goddess!

Thank you for letting me know you found this tip helpful. I'm so glad, because it sounds like you've got quite a task with those reports!

You might want to look into a text editor that supports regular expressions.

To overcome the limitation of replacing any space sequence you can use other characters. For example ^ indicates beginning of line. So:

Find : ^[\s]{2,2}
Replace with : \t

Says look for and replace any two spaces in the beginning of the line with one tab.

The following:
Find : ^[\s]{4,4}([\S])
Replace with : \t\t\1

Says to look for and replace any four spaces between the start of line and any non-space character with two tabs.

These expressions can become as complex as you require them for your reports and then just saved and rerun on every report.

This is a great lesson -- thank you for sharing this information!

I can't get this or Susan's suggestion to work with Office 2003.
Does it work only in Office 2007?

Did you check the "Use wildcards" option on the expanded ("More") screen? It didn't work for me until I did.

Thanks dperley. That worked for Susan's suggestion.
But I get a warning " ^ is not a valid special character" if I try Saurondor's suggestion in Word 2003.

that Saurondor refers to were refined in Unix for its many utilities that use pattern matching. They have been since been applied for use by Perl and other programs and are extremely flexible and powerful.
Microsoft products did not implement "regular expressions" as I know them. There are some similarities but for the most part IMHO, MS is in its own world regarding pattern matching. While useful, theirs pale when compared to "regular expressions".

Apparently Word doesn't have a way to detect the start of line with their regular expression syntax. If what you're working with is a text document you might want to download and install PSPad or similar tool. Otherwise for word documents Open Office supports a better range of regular expressions and can open .doc files.

Microsoft uses ^p for the paragraph marker and ^l for line breaks. One of these may work to search from the beginning of a line.

This is just one of several tricks for cleaning up those pesky reports. In the expression {2,}you can add a second figure after the comma to specify how many spaces to replace with the tab character. E.G., {3,7} will replace any string of 3 through 7 of the specified characters with the designated character. In the example above, you would replace the space character with a tab for every string of 3-7 spaces.

Take a good look at drop-down menu at the bottom centre marked "Special." The items here can greatly increase your search and replace functionality. One example: you can switch from a listing of "Doe, John" to "John Doe" using the 'expression' search function.

To get started, open Word, and in the Help menu, search for "wild cards" then In the resulting list, look at "Advanced Search Methods" and in the text, click on the "Wildcard characters you can use when searching." (Wording on the Mac - may vary slightly on the PC.)

Have fun.

"This replace trick will work in any Office application, not just Word." ... Ctrl+H in Outlook?

As far as I know, it works in all of the Office applicatins.

... not only does Ctrl+H not bring up the Find/Replace dialog, Replace isn't even on the Edit menu.

But the main thing is, this is going to save me a lot of work in Word, which I use to convert text-doc song sheets, where guitar chords have been placed over lyrics using spaces, to Word docs, where using tabs produces more accurate results. Thanks.

This doesn't work for me in either version of Word, I get a message saying invalid pattern search.

Are there any other settings that need to be adjusted as this would be a helpful tool.

Did you check the Use Wildcards option, as instructed?

Yes I checked the wildcard option. In fact I did it both ways I tried all sorts of other combinations and characters. Without wildcards checked it searches the document and finds nothing.

Are you absolutely certain that you have multple spaces in this particular document?

Yes, I typed them in myself. This is the message I get:
"The Find What text contains a Pattern Match expression which is not valid"

Are you certain that you entered a single space before the brace notation? The brace notation applies to the character/pattern that immediately precedes it. If there is nothing before it, you will get the error you mentioned.

You are correct, thanks for the tip, it works well. Guess I should have read the original script a little closer

I've been manipulating text in notepad for years but I have never used this principal. I have always used the keyboard to make a space or tab and then copy it into the fields depending on what was being replaced. You learn something new every day. Thanks Susan.

This is why I like browsing through TechRepublic.

There is great information in both the articles and in the comments that follow.

So thank you Susan and 'comment contributors'.

And thank you TechRepublic.

Is it just me or is there an Excel dialog box embedded on this post between steps 5 and 6?

Otherwise, thanks for a higher-tech way to do what I have done before which is to search and replace on multiple spaces. You can also just search on " " (without the quotes).