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

[rajtak]

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.

 

[Yongle]

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)

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

 

[Yongle]

without VBA, it is 4 steps

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