To replace a line break with a space or any other separator, enter the line break character in the Find what filed by pressing Ctrl + J. This shortcut is the ASCII control code for character 10 (line break, or line feed). Use the Find and Replace features in Excel to search for something in your workbook, such as a particular number or text string. You can either locate the se. Find and replace text or numbers Press Ctrl+H to open the Find and Replace dialog. The focus moves to the Find what text box in the Replace tab. In the Find what text box, type the text or numbers that you want to find and replace with something else. By default, the Find and Replace dialog is configured to look for the specified criteria anywhere in a cell, not to match the entire cell contents. For example, if you use 'AA' as your search criteria, Excel will return all the entries containing it such as AA-01, 01-AA, 01-AA-02, and so on.
Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
The Anchorage
Personal website of Gregory K. Maxey, Commander USN (Retired)
Do you have ad-blocking software enabled? While I respect your right to do so, your donations and the minimal advertisements on this site help to defray internet and other costs of providing this content. Please consider excluding this website from blocking or turning off the blocker while browsing this site.
DISCLAIMER/TERMS OF USE
The information, illustrations and code contained in my 'Microsoft Word Tips' are provided free and without risk or obligation.
However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.
If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!
The purpose of this Microsoft Word Tips & Microsoft Word Help page will show you how you can overcome the 255 character limit is Word built-in Find and Replace dialog 'Find what:' and 'Replace with:' text fields.
The 255 character replace string limit is easily overcome by copying the replace string text to the clipboard and using the character code '^c' in the 'Replace with:' window of the dialog box or as the .Replacement.Text value in you VBA procedure code.
Another handy method for defining any replacement text string and even a graphic is to use AutoText. Word MVP Graham Mayor and I worked out the VBA which Graham has posted on his web site here: Replace w/AutoText. Or use the replace with AutoText option in VBA Find & Replace.
Unfortunately the character code ^c doesn't work in the 'Find what' window. Even find strings constructed using wildcards are limited to 255 characters as shown below.
If you try to set a find or replace strings that are > 255 characters in length in VBA, a run-time error is generated.
I worked with Dave Lett and Helmut Weber, both former contributors the old Word VBA newsgroup, to develop a macro that would perform a find and replace routine where the text you are looking for exceeds 255 characters.
- The process involves defining the .Find.Text string as the first 250 characters of your text.
- If this segment is found, then the selection is extended to include the entire length of your specified text and then a comparison is made of the find text string and extended selection.
- If a match occurs, the selection.text is replaced with the specified replacement text.
In this solution, both the find and replace strings are defined in a separate document. Here is the macro code:
See: Installing Macros for instructions on how to set up and use the macros provided in this Microsoft Word Help & Microsoft Word Tips page.
That's it! I hope you have found this tips page useful and informative.
PAYMENTS/DONATIONS
Do you want to make a payment for consulting work or donate to help support this site?
PayPal is a safe, easy way to pay online.
Use the appropriate currency 'Donate' button to make a payment or donation.
Search my site or the web using Google Search Engine
For some reason, Excel won't allow me to paste into the find or replace dialog. This is more than a little inconvenient. Am I missing something? Has anyone seen this before? Is there a fix or workaround?
TIA!
- 4 Contributors
- forum5 Replies
- 418 Views
- 9 Years Discussion Span
- commentLatest PostLatest Postby logan.calder.9
Recommended Answers
Hello,
The usual things apply: OS version, Office version. How are you trying to paste? Please let us know the exact steps.
Christian
Jump to Post
Hi,
What are the contents of the copy? What are you selecting when you hit cmd-c or cmd-v? What I am getting at is are you trying to copy a graphic into a text cell, or something strange like that.
I have also found out that in XL …
Find Or Replace Text For Mac Excel File
Jump to Post
All 5 Replies
Hello,
The usual things apply: OS version, Office version. How are you trying to paste? Please let us know the exact steps.
Find Or Replace Text For Mac Excel Download
Christian