Comma to Trigger new row content

Try

"Cells.Replace _
        What:=""AQUATICS - FILTERS: SUMPS"", Replacement:=""177" & Chr(10) & "173"", _
    LookAt:=xlWhole, MatchCase:=True"

Wrap text needs to be true for that to work.

This won't exactly work for what I do. I do not enter each replacement manually I have this setup in a formula.

"Cells.Replace _
        What:=""AQUATICS - FILTERS: SUMPS"", Replacement:=""177,173"", _
    LookAt:=xlWhole, MatchCase:=True"
"Cells.Replace _
        What:=""AQUATICS - BULBS: FLOURESCENT"", Replacement:="""", _
    LookAt:=xlWhole, MatchCase:=True"
"Cells.Replace _
        What:=""AQUATICS - FISH BOWLS"", Replacement:=""183"", _
    LookAt:=xlWhole, MatchCase:=True"

for the numbers for example is use this code:

=Sheet1!I14 & IF(ISNUMBER(Sheet1!L14),"," & Sheet1!L14,"")

This pulls 177,173 from another sheet and if there is not another it will not include the comma and other number...So now what I need is if there is another number in sheet one example L14 it needs to go on the row below.

---------- Post added at 09:29 PM ---------- Previous post was at 09:13 PM ----------

And the code above does not put it in its own row

---------- Post added at 10:33 PM ---------- Previous post was at 09:29 PM ----------

basically I am looking for a if command that would move the content if there is anything in sheet1 like the if command above. I just need there to be no comma and go to the row below.

Thank you for the reply. To make it easier I just need it to take the content with a commma to a next new row.
I found a macro but it doesn't do exactly what I need. The script does not create a new "blank" row with just that content in it.


It divides this category which I can't have it do that:
Dog/Collars, Harnesses & Leashes

An attachment is attached as requested.

Option Explicit
Sub Macro1()
    Dim fromCol As String
    Dim toCol As String
    Dim fromRow As String
    Dim toRow As String
    Dim inVal As String
    Dim outVal As String
    Dim commaPos As Integer

    ' Copy from column A to column B.'
    fromCol = "A"
    toCol = "B"
    fromRow = "1"
    toRow = "1"

    ' Go until no more entries in column A.'
    inVal = Range(fromCol + fromRow).Value
    While inVal <> ""

        ' Go until all sub-entries used up.'
        While inVal <> ""
            Range(fromCol + fromRow).Select

            ' Extract each subentry.'
            commaPos = InStr(1, inVal, ",")
            While commaPos <> 0

                ' and write to output column.'
                outVal = Left(inVal, commaPos - 1)
                Range(toCol + toRow).Select
                Range(toCol + toRow).Value = outVal
                toRow = Mid(Str(Val(toRow) + 1), 2)

                ' Remove that sub-entry.'
                inVal = Mid(inVal, commaPos + 1)
                While Left(inVal, 1) = " "
                    inVal = Mid(inVal, 2)
                Wend
                commaPos = InStr(1, inVal, ",")
            Wend

            ' Get last sub-entry (or full entry if no commas).'
            Range(toCol + toRow).Select
            Range(toCol + toRow).Value = inVal
            toRow = Mid(Str(Val(toRow) + 1), 2)
            inVal = ""
        Wend

        ' Advance to next source row.'
        fromRow = Mid(Str(Val(fromRow) + 1), 2)
        Range(fromCol + fromRow).Select
        inVal = Range(fromCol + fromRow).Value
    Wend
End Sub

Yes I will not be using the numbers now I need to us the text. So in the "checkthis.xlsx" file is correct. I just need anything with a comma in the middle of the category to create a new row and then the text after the comma to go directly after it. The only issue is again the category with Dog/Collars, Harnesses & Leashes

I know there are alot of duplicated these are the categories for each product. The attached file is correct.

There is content left and right. The way this needs to upload is the items in column C with a comma needs to go to a new blank row. But it cannot carry any of the content in the new row that is to the left and right of it. Macro would probably be the best method? It needs to ignore Dog/Collars, Harnesses & Leashes where this is one category. New attachment is attached.

I'm still lost see if this works, there is some overkill in the code to allow for the possibility of multiple commas in 1 cell.

Sub test()
Dim c As Range, ctr As Long

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual

    With ActiveSheet
        For Each c In Intersect(.Range("C:C"), .UsedRange)
            ctr = Len(c) - Len(Replace(c, ",", ""))
            If ctr Then
                c.Offset(1).Resize(ctr, 1).EntireRow.Insert
                c.Resize(ctr + 1, 1) = Application.WorksheetFunction.Transpose(Split(c, ","))
            End If
        Next
        
        With Intersect(.UsedRange, .Range("B:D"))
             .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
             .Value = .Value
        End With
    End With

    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub