Wrap cell text without dividing any word characters into two lines-CodePudding

As you see on the above picture, I need to wrap the cell text without dividing any word characters into two lines.
Also I want to preserve the column width.
I made the issue words in bold to illustrate the problem.
These are initial cells text without any wrapping:
Pressure Vessel
Infra , Platfrom Facilities
Follow up Clamps , Weak Points
My actual dataset is 11k rows. grateful for all your help.

Sub Wraptext()
 
   Range("A2:A4").Wraptext = True
 
End Sub

CodePudding user response：

Your data might have 'bad spaces' in it, meaning Excel isn't correctly recognizing the space characters as the preferred place to split the text up. Try copying the space character between Pressure and Vessel, then find and replace with a normal space character.

Edit: Now that you know your spaces are 160, do a find a replace on alt 0160 with alt 0032

CodePudding user response：

The idea of this answer is fully go to @Ike and @Brennan.
I just convert it to VBA code to make it easier for anyone have the same issue.

Sub Replace_non_breaking_space()
 
'Chr(160) is the (non_breaking_Space)
'Chr(32) is the normal Space
 
  Dim ws As Worksheet
  Set ws = ActiveSheet  'adapt to your need
 
  Dim cel As Range
   For Each cel In ws.Range("A2:A4")
      cel = Application.Trim(Replace(cel, Chr(160), Chr(32)))
   Next cel
 
  ws.Range("A2:A4").WrapText = True
 
End Sub