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>

 

[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]