APPEND WITH TEXT AFTER EACH COMMA IN A CELL WITH A DESIRED TEXT

[meer_ali]

Hello Friends,

I'm trying to Append text with Prefix product to each text string after comma(,) within a cell. I'm trying with a formula of substitute but unable to achieve.

i'm using formulas as -

="product"&SUBSTITUTE(D5,CHAR(10),) where D4 has values ="PRODUCT"&SUBSTITUTE(D4,CHAR(10),) and ouput is as below:

PRODUCT9010, 9030, 9040, 9050

I needed as - PRODUCT9010, PRODUCT9030, PRODUCT9040, PRODUCT9050

Also, I tried with helper column set in F2 and
=IFERROR(FIND(CHAR(10),$D2,E2+1),LEN($D2))

=IFERROR("product"&RIGHT(LEFT($D2,F2-1),F2-E2-1)&"product"& CHAR(10),"")

But i'm getting ouput as below:

PRODUCT9010, 9030, 904PRODUCT

Can somebody help me in this regard, Appreciate your efforts. Thanks

APPEND TEXT AFTER EACH COMMA IN A CELL WITH DESIRED TEXT.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	X0				helper column formula for F2
2	9010, 9030, 9040		9010, 9030, 9040		16		PRODUCT9010, 9030, 904PRODUCT
3
4			9010, 9030, 9040, 9050				li9010, 9030, 904li	product9010, 9030, 9040
5
6
7								IFERROR("product"&RIGHT(LEFT($D2,F2-1),&"product"&mid($D2,F2-E2-1),"")
8
9							179010, 9030, 9040	IFERROR("product"&RIGHT(LEFT($D2,F2-1),&"product"&mid($D2,F2-E2-1),"")
10							product9010, 9030, 904
11
12
13	16								FIND(",",D2),F2-E2-1,"",)
14							=MID(A1,3)
15
16
17	"product"&SUBSTITUTE(D2,CHAR(10),CHAR(10),"product"&LEN(MID($D2,F2-1),F2-E2-1)&CHAR(10))
18
19	YES9010, 9030, 904
20	PRODUCT9010, 9030, 9040, 9050
21
22
23
24
work

Cell Formulas
Range	Formula
F2	F2	=IFERROR(FIND(CHAR(10),$D2,E2+1),LEN($D2))
H2	H2	=IFERROR("PRODUCT"&RIGHT(LEFT($D2,F2-1),F2-E2-1)&"PRODUCT"& CHAR(10),"")
I2:K2	I2	=IFERROR("li"&RIGHT(LEFT($D2,G2-1),G2-F2-1)&"/li"& CHAR(10),"")
H4	H4	=IFERROR("li"&RIGHT(LEFT($D2,F2-1),F2-E2-1)&"li"& CHAR(10),"")
I4	I4	="product"&D2
G6	G6	=IFERROR("<li>"&RIGHT(LEFT($D2,#REF!-1),#REF!-E2-1)&"</li>"& CHAR(10),"")
H9	H9	="17"&SUBSTITUTE(D2,CHAR(10),CHAR(10)&"17")
H10	H10	=IFERROR("product"&RIGHT(LEFT($D2,F2-1),F2-E2-1),"product"&MID($D2,LEFT($D2,F2-1),F2-E2-1))
B13	B13	=IFERROR(FIND(CHAR(10),$D2,E2+1),LEN($D2))
B19	B19	=IFERROR("YES"&RIGHT(LEFT($D2,F2-1),F2-E2-1)& CHAR(10),"")
B20	B20	="PRODUCT"&SUBSTITUTE(D4,CHAR(10),)

APPEND TEXT AFTER EACH COMMA IN A CELL WITH DESIRED TEXT.xlsx
	B
1	X0
work

 

[jasonb75]

Trying to pick something out of that mess, is this what you want?

="PRODUCT "&SUBSTITUTE(D2,", ",CHAR(10)&"PRODUCT ")

 

[mumps]

VBA approach assumes that the numbers are in column B and result will be place in column C. Change the range (in red) to suit your needs.

Sub AppendText()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, spltRng As Variant, i As Variant
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("B2:B" & LastRow)
         spltRng = Split(rng, ", ")
         For i = LBound(spltRng) To UBound(spltRng)
            If rng.Offset(, 1) = "" Then
                rng.Offset(, 1) = "PRODUCT" & spltRng(i)
            Else
                rng.Offset(, 1) = rng.Offset(, 1) & ", " & "PRODUCT" & spltRng(i)
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[meer_ali]

Trying to pick something out of that mess, is this what you want?

="PRODUCT "&SUBSTITUTE(D2,", ",CHAR(10)&"PRODUCT ")

Thanks alot for your effort. Your formula works,... absolutely good...

="PRODUCT"&SUBSTITUTE(D2,", ",CHAR(10)&","&"PRODUCT")

Thanks again for your quick response

 

[meer_ali]

Thanks alot for your efforts too for VBA code which is working like butter smooth just one-click.

Thanks once again for your quick response..:

VBA approach assumes that the numbers are in column B and result will be place in column C. Change the range (in red) to suit your needs.

Sub AppendText()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, spltRng As Variant, i As Variant
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("B2:B" & LastRow)
         spltRng = Split(rng, ", ")
         For i = LBound(spltRng) To UBound(spltRng)
            If rng.Offset(, 1) = "" Then





	
		
	
	

		
		

			VBA approach assumes that the numbers are in column B and result will be place in column C.  Change the range (in red) to suit your needs.

	
	
	
	
	
	



	
	

		
Sub AppendText()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range, spltRng As Variant, i As Variant
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("B2:B" & LastRow)
         spltRng = Split(rng, ", ")
         For i = LBound(spltRng) To UBound(spltRng)
            If rng.Offset(, 1) = "" Then
                rng.Offset(, 1) = "PRODUCT" & spltRng(i)
            Else
                rng.Offset(, 1) = rng.Offset(, 1) & ", " & "PRODUCT" & spltRng(i)
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub
	

		
		
	

                rng.Offset(, 1) = "PRODUCT" & spltRng(i)
            Else
                rng.Offset(, 1) = rng.Offset(, 1) & ", " & "PRODUCT" & spltRng(i)
            End If
        Next i
    Next rng
    Application.ScreenUpdating = True
End Sub

VBA approach assumes that the numbers are in column B and result will be place in column C. Change the range (in red) to suit your needs.

 

[mumps]

Glad to help.