Search code examples
excelvba

EXCEL VBA : Wrap Cell Text at certain Location

This is sample data :

Item
Item#1 * ItemName_1
Item#2 * ItemName_2

I need to wrap text at the "*" like this

Item#1
ItemName_1

Item#2
ItemName_2

This is what i tryed after browsing Stackoverflow and others :

Sub BreakAndWrap()    
Dim rng As Range
Set rng = Range("A1", Range("A250").End(xlUp))

For Each cell In rng
    cell.Value = Replace(cell.Value, "*", ChrW(8209))
    cell.WrapText = True
Next

End Sub

This Replaces "*" by "-" and wrap but manually and at randome location not at hyphen.

Solution

  • Replace it with the Chr(10) character.

    Sub BreakAndWrap()    
Dim rng As Range
Set rng = Range("A1", Range("A250").End(xlUp))

For Each cell In rng
    cell.Value = Replace(cell.Value, "*", Chr(10))
    cell.WrapText = True
Next
End Sub

    ChrW(8209) is the non-breaking hyphen.