Delete comma at end of cell using Find & Replace

[Jubinell]

Hi,

I have a series of cells. Some of them end with the comma, and some do not. Some have really weird stuff in them (like manual line breaks).

Now I would like to go through each of the cells, and delete the ending comma when it occurs. Can I do this using the Find and Replace feature?

Note: there may be comma(s) in a cell but it/they is/are not always the last character of the cell.

Thank you in advance for your help!

 

[id107]

Edit.

No don't use what i put. Didn't read that there were commas in the middle. Oops.

 

[id107]

try this

Sub test()
 
    Dim c As Range, lr As Long, lc As Long
 
    lr = Cells.SpecialCells(xlCellTypeLastCell).Row
    lc = Cells.SpecialCells(xlCellTypeLastCell).Column
 
    For Each c In Range(Cells(1, 1), Cells(lr, lc))
        If Right(c.Value, 1) = "," Then c.Value = Left(c.Value, Len(c.Value) - 1)
    Next c
 
End Sub

 

[Jubinell]

Thanks...that does the job. I was hoping I could use some powerful features embedded in Excel's Find and Replace though.

 

[Jon von der Heyden]

Nothing in the find and repace I'm afraid, but equally you could use a formula:

=LEFT(A1,LEN(A1)-(RIGHT(A1,1)=","))