Hi,

Something like that. Here it does it for A1:A5.
You could write to column at the right:
just replace c = chgstr(c) with c(1,2)=chgstr(c)

You'll need to adapt as I'm not sure about the 1 or 2 (what's a trailing letter?
At the end of the string, the word, before another capital letter?)

Function ChgStr(ByVal s As String) As String
Dim i%, j%, C As String
j = Len(s)
For i = 1 To j
C = Mid(s, i, 1)
If C Like "[A-Z]" Then C = " " & C
If (C = "1" Or C = "2") And i = j Then C = C & " "
ChgStr = ChgStr & C
Next i
End Function

Sub ProcessARange()
Dim C As Range
For Each C In Range("A1:A5") ' adapt
C = ChgStr(C)
Next C
End Sub

Regards,

Daniel M.

"Ray Batig" wrote in message
link.net...
I have a whole lot of strings to work with. They are arranged in a column.
Examples are ....aaThisIsATest1... and ...aaThisOneIsSpecial... without the
....'s.
I can write a macro that strips off the leading aa's and replaces them
with one space. What I need to do is at every capital letter, insert a
space, and if the trailing letter is a 1 or 2, insert a space. I have found
functions which convert to upper or lower cases, however, not identify
them.


How would I write a macro to accomplish this task?

Thanks in advance for helping!

Ray