Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Manipulation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Manipulation
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Manipulation
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
String Manipulation
Works perfectly.
Thanks!!!! Jake Marx wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value manipulation | Excel Discussion (Misc queries) | |||
string manipulation, pulling out last name from column | Excel Worksheet Functions | |||
Text string manipulation... | Excel Worksheet Functions | |||
String Manipulation within VBA | Excel Discussion (Misc queries) | |||
String Manipulation | Excel Discussion (Misc queries) |