Search code examples
excelvbastringword-wrap

Wrap cell text without dividing any word characters into two lines

enter image description here

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
Solution

  • 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