Excel - removing space character from string.

[MuskyMan]

I need to clean it up a spreadsheet. One issue I'm having is 1,800 cells with text. Sample of the text:

' C5545544-Taxes
' M133rt-Closed
' 455123-CAM charges

I need to strip the ' and spaces. I've trid MID but with different number of spaces it's a pain.

Any thoughts on what to use??

Thanks in advance

 

[mharroff]

Try using the SUBSTITUTE function.

=SUBSTITUTE(SUBSTITUTE(A1," ",""),"'","")

 

[AnotherHiggins]

If they are really spaces, then
[COLOR=blue white]=Trim(A1)[/color]
will work.

If that doesn't work, then you have characters other than spaces in your string. But don't worry, you can use a macro solution. This little baby has saved me tons of time:

Sub TrimALL()
   'David McRitchie 2000-07-03
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
      Dim cell As Range
      'Also Treat CHR 0160, as a space (CHR 032)
       Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
       LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
      'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next   'in case no text cells in selection
      For Each cell In Intersect(Selection, _
         Selection.SpecialCells(xlConstants, xlTextValues))
         cell.Value = Application.Trim(cell.Value)
      Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

Just select the entire column and run this macro. I have it mapped to a button on a custom toolbar for easy reference.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[macropod]

Hi MuskyMan,

If you need to retain the internal spacing, as in converting:
' 455123-CAM charges
to:
455123-CAM charges
you'll need a modification of mharroff's solution. Try:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," "),"'",""))
This approach also deals with non-breaking spaces (chr 160) that some sytems output.

Cheers