How to find and replace text then preserve formatting in Excel?
Find and replace preserve formatting with Macro code
Navigation pane -- Find and Replace
|Kutools for Excel's advanced Find and Replace function, can help you find and |
replace a value across multiple sheets and workbooks.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
There is no other methods but macro code can help you to find and replace text and preserve formatting.
1. Press Alt + F11 keys to open the Microsoft Visual Basic for Application window.
2. Click Insert > Module, and paste below code to the script.
VBA: Find and replace preserve formatting
Sub CharactersReplace(Rng As Range, FindText As String, ReplaceText As String, Optional MatchCase As Boolean = False) 'UpdatebyExtendoffice20160711 Dim I As Long Dim xLenFind As Long Dim xLenRep As Long Dim K As Long Dim xValue As String Dim M As Long Dim xCell As Range xLenFind = Len(FindText) xLenRep = Len(ReplaceText) If Not MatchCase Then M = 1 For Each xCell In Rng If VarType(xCell) = vbString Then xValue = xCell.Value K = 0 For I = 1 To Len(xValue) If StrComp(Mid$(xValue, I, xLenFind), FindText, M) = 0 Then xCell.Characters(I + K, xLenFind).Insert ReplaceText K = K + xLenRep - xLenFind End If Next End If Next End Sub Sub Test_CharactersReplace() Dim xRg As Range Dim xTxt As String Dim xCell As Range On Error Resume Next If ActiveWindow.RangeSelection.Count > 1 Then xTxt = ActiveWindow.RangeSelection.AddressLocal Else xTxt = ActiveSheet.UsedRange.AddressLocal End If Set xRg = Application.InputBox("Select a range:", "Kutools for Excel", xTxt, , , , , 8) If xRg Is Nothing Then Exit Sub Call CharactersReplace(xRg, "KK", "Kutools", True) End Sub
3. Press F5 key, a dialog pops out for you to select a range to find and replace, see screenshot:
4. Click OK, and the specific string in the selected cell are replace with other and keep the formatting.
Tip: In the code, KK is the string you want to find, and Kutools is the string you want to replace with, you can change them as you need.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 8 days agoCan you clarify if there is a solution that works in cells with more than 255 characters, or is this a limitation of Excel? Does your add-in support cells with more than 255 characters that contain formatting?
- To post as a guest, your comment is unpublished.· 1 months agoSame Question as John Birk i need a workaround with Cells that have more then 255 signs.
- To post as a guest, your comment is unpublished.· 1 months agoThe method only work while characters less than 255 digits.
- To post as a guest, your comment is unpublished.· 4 months agoI am getting syntax error in VBA
- To post as a guest, your comment is unpublished.· 4 months agoI tried your solution on a cell that is over 255 characters long and nothing changes. Is there a workaround for this case?