String Manipulation

  • Thread starter Thread starter Ray Batig
  • Start date Start date
R

Ray Batig

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
 
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]
 
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.
 
Works perfectly.

Thanks!!!!

Jake Marx said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top