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

@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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies