'Clear Formats' does not clear all formats when only a part of text string is formatted in a cell.

rajtak

Board Regular
Joined
Feb 23, 2009
Messages
74
I use Excel 2013. The option "Home > Clear > Clear Formats" does not clear the formats when a part of text string is formatted in a cell. Please find a screenshot below and let me know, is there any option available to clear formats in such cases? Thank you in advance.

 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,787
Office Version
  1. 365
Platform
  1. Windows
Here is VBA that can do what you want
- attach to a shortcut to clear "text" formatting in selected cells
(cells containing formulas are ignored)

Code:
Sub ClearFormat()
    On Error Resume Next
    For each c in Selection.SpecialCells(xlCellTypeConstants)
        c.ClearFormats
        c.Value = c.Value
    Next
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,787
Office Version
  1. 365
Platform
  1. Windows
without VBA, it is 4 steps

copy A1
paste value only to B1 (= any cell without any formatting)
copy B1
paste to A1
 

Watch MrExcel Video

Forum statistics

Threads
1,109,472
Messages
5,529,035
Members
409,849
Latest member
J7House1984
Top