Removing comma but only if nothing follows it

[otictac1]

Hello everyone,

Is there anyway to remove a comma, but only in the case where nothing would follow it. I have some data that can come in two varieties. The first:

Data, data

Is just fine the way it is.

The second type:

Data,

Is okay, but would look much neater if I could remove the superflous comma. The data length is variable and I am doing this in a much larger VBA code that will analyze quite a few cells on a worksheet.

Is this possible?

Thanks in advance.

 

[erik.van.geit]

Hi,

Can this get you started?

Sub test()
Dim c As Range
Set c = Range("A1")
If Right(c, 1) = "," Then c = Left(c, Len(c) - 1)
End Sub

kind regards,
Erik

 

[Jonmo1]

Try this

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

Hope that helps.

 

[Joe4]

Here is some VBA code that loops through a specified range and removes the trailing commas.

Sub RemoveTrailingCommas()

    Application.ScreenUpdating = False
    
    Dim myRange As Range
    Dim cell As Range
    
'   Set Range to loop through
    Set myRange = Range("A1:A3")

'   Loop through data, updating where necessary
    For Each cell In myRange
        If Right(cell, 1) = "," Then cell = Left(cell, Len(cell) - 1)
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[mikerickson]

(wrong, faulty thinking on my part)

 

[otictac1]

Thank you all so much. This really helped.