TRIMING CELLS

JonRowland

Active Member
Joined
May 9, 2003
Messages
415
Office Version
  1. 365
Platform
  1. Windows
I know that the TRIM function can remove unwanted spaces with the text of a cell but it there anyway of doing this with say commas?

Thanks

Jon
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Ah yes it would be but the commas are not if given place and may be one (which I would like to keep) or two or three or even more.

I can do it manually but takes so long.

Jon
 
Upvote 0
If you can devise some sort of "rule" regarding which commas to get rid of, then something can probably written to do this. But you have to have some methodology for determining which ones to keep and which ones to get rid of.
 
Upvote 0
The rule would be to replace any commas that appear more than once in sucession, to remove any comma at the start or end of the text.

HTH

Jon
 
Upvote 0
Try:

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(A1," ","@"),","," "))," ",","),"@"," ")

(if you have @ in your text, change those to something else that you don't have in your strings)
 
Upvote 0
I see you got an answer that works, so this is probably a moot point, but since I took the time to write it, I'll post it anyway.

Here is a macro that automatically "fixes" the commas for all the rows in the column the active cell is in:

Code:
Sub FixCommas()
    
    Application.ScreenUpdating = False
    
'   Get rid of multiple commas
    Dim myColumn As Byte
    myColumn = ActiveCell.Column

    Columns(myColumn).Replace What:=",,", Replacement:=",", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False

'   Get rid if commas at the beginning and end
    Dim i As Long
    For i = 1 To 65536
        If Left(Cells(i, myColumn), 1) = "," Then
            Cells(i, myColumn) = Right(Cells(i, myColumn), Len(Cells(i, myColumn)) - 1)
        End If
        If Right(Cells(i, myColumn), 1) = "," Then
            Cells(i, myColumn) = Left(Cells(i, myColumn), Len(Cells(i, myColumn)) - 1)
        End If
    Next i
    
    Application.ScreenUpdating = True

End Sub

If you run it, it automatically fixes the entries without creating any new columns or formulas.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top