Replace Carriage Returns & Remove Last Comma

[Nanogirl21]

Using VBA is there a way to replace carriage returns with a comma space?

Also, if the value ends with comma space or comma i''d like to delete those value.

This will be done on sheet called Cranberries in coulmn E only.

EXAMPLE

CURRENT	WANTED RESULTS
APPLE JUICE GRAPE JUICE ORANGE JUICE,	APPLE JUICE, GRAPE JUICE, ORANGE JUICE
HOT DOG BURGER	HOT DOG, BURGER
BLUE GREEN YELLOW,	BLUE, GREEN, YELLOW
WINTER SUMMER SPRING FALL,	WINTER, SUMMER, SPRING, FALL
HOT, COLD, WARM, UNKNOWN	HOT, COLD, WARM, UNKNOWN

<tbody>
</tbody>

 

[Oscar Cronquist]

	A​	B​	C​	D​	E​	F​	G​
1​	APPLE JUICE GRAPE JUICE ORANGE JUICE,	APPLE JUICE, GRAPE JUICE, ORANGE JUICE
2​

Sheet: Sheet26

User defined function in cell B1:
=ReplaceReturn(A1)

Function ReplaceReturn(rng As Range)
Dim str As String
str = Trim(Replace(rng, Chr(10), ", "))
If Right(str, 1) = "," Then
    ReplaceReturn = Left(str, Len(str) - 1)
Else
    ReplaceReturn = str
End If
End Function

 

[Fluff]

How about

Sub repl()
   With Range("E1", Range("E" & Rows.Count).End(xlUp))
      .Replace Chr(10), ", ", xlPart, , , , False, False
      .Value = Evaluate(Replace("if(right(@,1)="","",left(@,len(@)-1),@)", "@", .Address))
   End With
End Sub

 

[Nanogirl21]

How about

Sub repl()
   With Range("E1", Range("E" & Rows.Count).End(xlUp))
      .Replace Chr(10), ", ", xlPart, , , , False, False
      .Value = Evaluate(Replace("if(right(@,1)="","",left(@,len(@)-1),@)", "@", .Address))
   End With
End Sub

Thank you. This removed the return, but it leaves comma space (, ) at the end. Also, it placed some comma space at the beginning of some cells.

 

[Nanogirl21]

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​	APPLE JUICE GRAPE JUICE ORANGE JUICE,	APPLE JUICE, GRAPE JUICE, ORANGE JUICE
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​

<tbody>
</tbody>

Sheet: Sheet26

<tbody>
</tbody>

User defined function in cell B1:
=ReplaceReturn(A1)

Function ReplaceReturn(rng As Range)
Dim str As String
str = Trim(Replace(rng, Chr(10), ", "))
If Right(str, 1) = "," Then
    ReplaceReturn = Left(str, Len(str) - 1)
Else
    ReplaceReturn = str
End If
End Function

<g class="gr_ gr_22 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" id="22" data-gr-id="22">Ive</g> never used function VBA codes. Not sure where to define the sheet or function.

 

[Fluff]

How about

Sub repl()
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Replace Chr(10), ", ", xlPart, , , , False, False
      .Value = Evaluate(Replace("if(right(@,2)="", "",left(@,len(@)-2),@)", "@", .Address))
      .Value = Evaluate(Replace("if(left(@,2)="", "",right(@,len(@)-2),@)", "@", .Address))
   End With
End Sub

 

[Oscar Cronquist]

Ive never used function VBA codes. Not sure where to define the sheet or function.

The following webpage explains how to use user defined functions:
https://www.vertex42.com/blog/excel-formulas/custom-user-defined-functions.html

 

[Nanogirl21]

perfect. thank you.

 

[Rick Rothstein]

Here is another macro that you can consider...

[table="width: 500"]
[tr]
	[td]Sub ReplaceLFwithCommaSpaceAfterRemovingTrailingComma()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1) = Evaluate(Replace("IF({1},SUBSTITUTE(LEFT(TRIM(@),LEN(@)-(RIGHT(@)="","")),CHAR(10),"", ""))", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Nanogirl21]

Here is another macro that you can consider...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ReplaceLFwithCommaSpaceAfterRemovingTrailingComma()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Offset(, 1) = Evaluate(Replace("IF({1},SUBSTITUTE(LEFT(TRIM(@),LEN(@)-(RIGHT(@)="","")),CHAR(10),"", ""))", "@", .Address))
  End With
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for this version of the code. Currently it is placing the results in the column to the right. Is there any way to keep the results in the current column? My data is in column E.

Also, some of my cells start with ", " (comma space without the "). Is there a way to delete these two leading charaters if the cell start with those? I can't do a simple find and replace cause I may have the comma space later in the cell. I just want to remove if the cell starts with that text.