Home

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
anthonyjb
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

@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)

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies