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
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]CURRENT
[/TD]
[TD]WANTED RESULTS
[/TD]
[/TR]
[TR]
[TD]APPLE JUICE
GRAPE JUICE
ORANGE JUICE,
[/TD]
[TD]APPLE JUICE, GRAPE JUICE, ORANGE JUICE
[/TD]
[/TR]
[TR]
[TD]HOT DOG
BURGER
[/TD]
[TD]HOT DOG, BURGER
[/TD]
[/TR]
[TR]
[TD]BLUE
GREEN
YELLOW,
[/TD]
[TD]BLUE, GREEN, YELLOW
[/TD]
[/TR]
[TR]
[TD]WINTER
SUMMER
SPRING
FALL,
[/TD]
[TD]WINTER, SUMMER, SPRING, FALL
[/TD]
[/TR]
[TR]
[TD]HOT, COLD, WARM, UNKNOWN
[/TD]
[TD]HOT, COLD, WARM, UNKNOWN
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

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.

See if this code does what you want...

[table="width: 500"]
[tr]
	[td]Sub ReplaceLFwithCommaSpaceAfterRemovingTrailingComma()
  Dim Txt As Variant
  Application.ScreenUpdating = False
  With Range("E1", Cells(Rows.Count, "E").End(xlUp))
    .Value = Evaluate(Replace("IF({1},SUBSTITUTE(LEFT(TRIM(@),LEN(@)-(RIGHT(@)="","")),CHAR(10),"", ""))", "@", .Address))
    .Value = Evaluate(Replace("IF({1},IF(LEFT(@)="","",TRIM(MID(@,2,LEN(@)-1)),@))", "@", .Address))
  End With
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]