Excel: Removing spaces at the start and / or end of text in cells 2

[keywordmonkey]

Hi

I've got lots of sheets with text in cell A that has spaces at the start/end/both, of the text.

Currently I can remove this by pasting into Word, justifying into the centre, to the right and then to the left (the only clumsy solution I've found).

I'd like to either use a macro to do this in Excel, or if that's not possible have Word open hidden in the background, justify as described above, and paste back into Excel.

On other occasions, I often need to change the case of all the text in a column in Excel to sentence case, but Excel only does uppercase/lowercase/Capitalise Every Word. Currently I do this by pasting into Word too and using Format>Change Case.

I've found passing references to using Word functions via Excel VBA, but don't know where to start.

So I need help! Thanks in advance.

 

[Zathras]

The following macro will trim leading and trailing spaces from all non-formula cells in the active worksheet. (If you really want to restrict it to a particular column, see the technique in the next macro below.)

Option Explicit
Sub TrimAll()
Dim c As Range
  For Each c In ActiveSheet.UsedRange
    If Not c.HasFormula Then
      c.Value = Trim(c.Value)
    End If
  Next c
End Sub

The following macro will capitalize the first word of every sentence found in the specified column (designated by letter):

Sub CapitalizeSentences(Column As String)
Dim col As Range
Dim c As Range
Dim nColNum As Integer
Dim bUpdated As Boolean
Dim sWork As String
Dim nPeriod As Integer
Dim nX As Integer
Dim nChar As Integer
  nColNum = Asc(Column) - 64
  With ActiveSheet
    Set col = Intersect(.Columns(nColNum), .UsedRange)
  End With
  For Each c In col
    sWork = c.Value
    bUpdated = False
    nX = 1
    nPeriod = InStr(sWork, ".")
    If nPeriod > 0 Then
      While nPeriod > 0

        ' sWork contains a sentence from nX to nPeriod
        ' Find first non-blank letter and upper-case it.

        While nX < nPeriod And Mid(sWork, nX, 1) = &quot; &quot;
          nX = nX + 1
        Wend
        nChar = Asc(Mid(sWork, nX, 1))
        If nChar >= 97 And nChar <= 122 Then
          Mid(sWork, nX, 1) = Chr(nChar - 32)
          bUpdated = True
        End If

        ' Find next sentence in sWork.

        nX = nPeriod + 1
        nPeriod = InStr(nX, sWork, ".")
      Wend
      If bUpdated Then c.Value = sWork
    End If
  Next c
  Set col = Nothing
End Sub

This sub can be used to test the previous sub:

Sub test()
  CapitalizeSentences "A"
End Sub

Hope this helps.

 

[Gavona]

=Trim(A1) will remove spaces at beginning and end.

You could create a macro to do this in VBA.

I'll bet someone could help with the sentence case problem (in which case I'll add the solution to my library)

 

[Gavona]

Thanks Zathras.

 

[bryanbayfield]

One thing to be careful of - don't use trim on cells that contain a date - they get mangled.

 

[keywordmonkey]

Cheers all for this - hope to do a FAQ one day on manipulating text in Excel. Will include this & post to the site.