TRIMING CELLS

JonRowland

Active Member
Joined
May 9, 2003
Messages
376
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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

JonRowland

Active Member
Joined
May 9, 2003
Messages
376
Office Version
  1. 365
Platform
  1. Windows
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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

What would be the rule for which to keep and which to delete?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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.
 

JonRowland

Active Member
Joined
May 9, 2003
Messages
376
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,982
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,046
Messages
5,639,757
Members
417,108
Latest member
Thein Than

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
Top