VBA to add newline before every instance of a word within a string

[steve1040]

Hello,
I have a a sheet with 500+ rows
One column(Directions) in the sheets contains a string. This string contains directions for the item in the row.
I'm looking for VBA code that will insert a newline everytime the word Step(some number here): Note the number of steps can be different for each row

Example string
Step1: Beat 2 eggs lightly in a large bowl. Stir in rice, Parmesan cheese, butter, cheese sprinkle, 1/2 teaspoon salt, and 1/4 teaspoon pepper; mix to combine. Cover and refrigerate the mixture for 20 minutes. Step2: Preheat an air fryer to 370 degrees F (187 degrees C) according to manufacturer's instructions. Step3: Roll mixture into 1 1/2-inch balls. Press a mozzarella cube into the center of each ball and reshape. Step4: Combine bread crumbs, Italian seasoning, salt, and pepper in a shallow dish. Beat remaining egg lightly in a second bowl. Dip each rice ball first into egg, then roll in the bread crumb mixture. Place rice balls in air fryer basket and spray with cooking spray. Step5: Cook in the preheated air fryer for 6 minutes. Increase temperature to 400 degrees F (200 degrees C) and air fry for 3 more minutes.

Desired string
Step1: Beat 2 eggs lightly in a large bowl. Stir in rice, Parmesan cheese, butter, cheese sprinkle, 1/2 teaspoon salt, and 1/4 teaspoon pepper; mix to combine. Cover and refrigerate the mixture for 20 minutes.

Step2: Preheat an air fryer to 370 degrees F (187 degrees C) according to manufacturer's instructions.

Step3: Roll mixture into 1 1/2-inch balls. Press a mozzarella cube into the center of each ball and reshape.

Step4: Combine bread crumbs, Italian seasoning, salt, and pepper in a shallow dish. Beat remaining egg lightly in a second bowl. Dip each rice ball first into egg, then roll in the bread crumb mixture. Place rice balls in air fryer basket and spray with cooking spray.

Step5: Cook in the preheated air fryer for 6 minutes. Increase temperature to 400 degrees F (200 degrees C) and air fry for 3 more minutes.

Thanks

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is that 'Example string' all in a single cell or several cells?
Is the desired result all in a single cell or several cells?

A good way to show us clearly what you have, where and what you want, where is to use XL2BB

 

[steve1040]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is that 'Example string' all in a single cell or several cells?
Is the desired result all in a single cell or several cells?

A good way to show us clearly what you have, where and what you want, where is to use XL2BB

The string is in a single cell, Would like the output in a single cell
See images

Thanks again
Steve

 

[Peter_SSs]

See images

Unfortunately, unlike XL2BB, I cannot copy from those images to have a good test, but try this. It assumes the table name is "Table1". Adjust if required.

Sub StepsOnNewLine()
  Dim a As Variant
  Dim i As Long
  
  With Range("Table1[directions]")
    a = .Value
    For i = 1 To UBound(a)
      a(i, 1) = Left(a(i, 1), 1) & Replace(a(i, 1), "Step", vbLf & "Step", 2)
    Next i
    .WrapText = True
    .Value = a
  End With
End Sub

 

[Micron]

Your posted desired result and your pic desired result is not the same. See if this gets you close for at least one cell (F1) on Sheet7 or whatever you change those to:

Sub AddBreaks()
Dim i As Integer, n As Integer, c As Integer
Dim strIn As String, strOut As String

i = 2: n = 1: c = 1
strIn = Sheets("Sheet7").Range("F1")

Do Until n = 0
   n = InStr(n, strIn, "Step" & i & ":")
   If n > 0 Then
       strOut = strOut & Mid(strIn, c, n - c) & Chr(13) & Chr(10)
       i = i + 1
       c = n
   End If
Loop
Debug.Print strOut

End Sub