New Line Break within a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-644685%22%20slang%3D%22en-US%22%3ENew%20Line%20Break%20within%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644685%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20way%20to%20show%20Line%20Break%20symbol%20and%20other%20hidden%20characters%20in%20a%20cell%26nbsp%3Bsimilar%20to%20how%20Word%20does%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-644685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644736%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Line%20Break%20within%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644736%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348359%22%20target%3D%22_blank%22%3E%40anthonyjb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Eok%20i%20didn't%20know%20such%20a%20function%2C%20but%20the%20following%20macro%20color%20all%20cells%20in%20selection%20with%20Line%20breaks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20ColorLineBreak()%3CBR%20%2F%3EDim%20rngCell%20As%20Range%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20Each%20rngCell%20In%20Selection%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20InStr(rngCell.Text%2C%20vbLf)%20%26gt%3B%200%20Then%3CBR%20%2F%3ErngCell.Interior.ColorIndex%20%3D%203%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3E%3CBR%20%2F%3ENext%20rngCell%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%26nbsp%3B%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.vba-Tanker.com%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ewww.vba-Tanker.com%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644747%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Line%20Break%20within%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348359%22%20target%3D%22_blank%22%3E%40anthonyjb%3C%2FA%3E%26nbsp%3B%2C%20nope%2C%20not%20such%20way.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20workaround%20you%20may%20apply%20conditional%20formatting%20to%20highlight%20entire%20cell%20with%20non-printable%20characters%20with%20the%20rule%20like%20%3DA1%26lt%3B%26gt%3BCLEAN(A1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-645732%22%20slang%3D%22en-US%22%3ERe%3A%20New%20Line%20Break%20within%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-645732%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20suggestions.%26nbsp%3B%20Microsoft%20needs%20to%20make%20that%20an%20option%20like%20do%20in%20Word%20instead%20of%20me%20writing%20custom%20formulas%20to%20find%20these%20hidden%20items.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Is there a way to show Line Break symbol and other hidden characters in a cell similar to how Word does?

3 Replies
Highlighted

@anthonyjb 

Hi,

ok i didn't know such a function, but the following macro color all cells in selection with Line breaks.

 

Sub ColorLineBreak()
Dim rngCell As Range

For Each rngCell In Selection

If InStr(rngCell.Text, vbLf) > 0 Then
rngCell.Interior.ColorIndex = 3
End If

Next rngCell


End Sub

 

Best regards 

Bernd

www.vba-Tanker.com

Highlighted

@anthonyjb , nope, not such way.

 

As workaround you may apply conditional formatting to highlight entire cell with non-printable characters with the rule like =A1<>CLEAN(A1)

Highlighted

Thanks for the suggestions.  Microsoft needs to make that an option like do in Word instead of me writing custom formulas to find these hidden items.