add a manual line break before each and every substring that starts with 'X0' using VBA

Dear Experts:

I would like to run a macro on selected cells that perform the following action:

The macro is to add a manual line break before each and every substring that starts with 'X0****'

 add_manual_line_breaks
The macro is to run these actions in the cell itself not copying the cell contents to the right and perform the action there.

The cell height has to be adjusted depending on the number of line breaks that get inserted.

Help is very much appreciated. I have attached a sample file for your convenience.

Regards, Andreas

Insert_manual_link_break_before_eve.xlsx
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
You don't need VBA to do it. You can do it with Find and Replace.

Select the cells containing the values.
Press Ctrl + H to bring up the Find & Replace Dialogue

In Find:   X0
In Replace:  press and hold Alt while typing 010 with number pad and then type the X0.   The X0 will probably disappear below the boundary of the box.

Click replace All

Alt 010 is the ASCII code for carriage return.

Just looked at the sample, why are you not inserting the line break before X0743_Pos32 in each of the cells?

If you include a space before the X0 in the find box, it will not enter the Carriage Return at the start of the cell.

If it is just X0743 that is to be ignored you can then repeat the Find and Replace to get rid of the Carriage Return before that string.

Find:  Alt 010 X0743
Replace: {space} X0743

Set the cell format to Wrap Text and then you can then adjust the row height and column width manually with double clicking the column/row header boundaries.
Martin LissOlder than dirtCommented:
Here is VBA code.

Sub InsertBreaks()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strParts() As String
Dim intLine As Integer

lngLastRow = Range("A1048576").End(xlUp).Row
For lngRow = 1 To lngLastRow
    strParts = Split(Cells(lngRow, "A"), "X0")
    If UBound(strParts) > 0 Then
        Cells(lngRow, "A") = ""
        For intLine = 1 To UBound(strParts)
            If intLine < UBound(strParts) Then
                Cells(lngRow, "A") = Cells(lngRow, "A") & "X0" & strParts(intLine) & vbCrLf
            Else
                Cells(lngRow, "A") = Cells(lngRow, "A") & "X0" & strParts(intLine)
            End If
        Next
        Cells(lngRow, "A").EntireRow.AutoFit
    End If
Next
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I just noticed that you seem to want the first two "X0"s to be on the same line. If so then use this code instead.
Sub InsertBreaks()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strParts() As String
Dim intLine As Integer

lngLastRow = Range("A1048576").End(xlUp).Row
For lngRow = 1 To lngLastRow
    strParts = Split(Trim(Cells(lngRow, "A")), "X0")
    If UBound(strParts) > 0 Then
        Cells(lngRow, "A") = ""
        For intLine = 1 To UBound(strParts)
            Select Case True
                Case intLine = 1
                    Cells(lngRow, "A") = "X0" & strParts(1) & " " & "X0" & strParts(2) & vbCrLf
                Case intLine = 2
                    ' Already used
                Case intLine < UBound(strParts)
                    Cells(lngRow, "A") = Cells(lngRow, "A") & "X0" & strParts(intLine) & vbCrLf
                Case Else
                    Cells(lngRow, "A") = Cells(lngRow, "A") & "X0" & strParts(intLine)
            End Select
        Next
        Cells(lngRow, "A").EntireRow.AutoFit
    End If
Next
End Sub

Open in new window

Andreas HermleTeam leaderAuthor Commented:
As a matter of fact you both deserve 1000 points. Great job from both of you. I really appreciate it. Regards, Andreas

T H A N K    Y O U    very much
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.