Hi Ray,

You could try a user-defined function (UDF) in VBA. Here's one example that
may fit your needs:

Public Function FixString(vData As Variant) As String
Dim nChar As Integer
Dim sChar As String * 1
Dim sReturn As String

For nChar = 1 To Len(vData)
'/ insert space if capital letter, number, or symbol
sChar = Mid$(vData, nChar, 1)
If IsNumeric(sChar) Or (sChar = UCase$(sChar)) Then
sReturn = sReturn & " " & sChar
Else
sReturn = sReturn & sChar
End If
Next nChar

FixString = Trim$(sReturn)
End Function


This doesn't handle the "aa" at the beginning of the string, but you could
handle that at the beginning or end of the function using Replace$(),
Instr(), or a similar function. To use this, just place the code in a
standard module, then call it from a worksheet as follows:

=FixString(A1)

or

=FixString("ABigTestOfFixString2")

Obviously, this won't take care of all instances, but you should be able to
check the results and add logic as needed. If you need any further
assistance with it, let us know.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Ray Batig wrote:
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