CODEWITHSUNDEEP
excelvbacharcellword-wrap
Hussein
Hussein

Reputation: 995

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.

Upvotes: 0

Views: 58

Answers (2)

taller
taller

Reputation: 18762

  • Don't need to use For loop
Sub BreakAndWrap()
    Dim rng As Range
    Set rng = Range("A1", Range("A250").End(xlUp))
    rng.Replace What:=" * ", Replacement:=Chr(10), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Upvotes: 2

Black cat
Black cat

Reputation: 6097

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.

Upvotes: 1

Related Questions