Hi,
Can Someone Help me.
I have looked all over the Web and there is lots on Propercase, but I could not find anything.
I have a form where I want to convert text to ProperCase (to this Eg:Replace 1400-P-411 Gate Valve, Replace 2123-LV-007 ByPass Spool Valves (Train 3), Replace 8300-FG-811 Check Valve)
I was using StrConv but on any Hyphen/Brackets that are before and after some letterrs were converted to LowerCase. I want them to be UpperCase.
(StrConv changes to this Eg:Replace 1400-p-411 Gate Valve, Replace 2123-lv-007 ByPass Spool Valves (Train 3), Replace 8300-fg-811 Check Valve)
Private Sub WorkOrderDescription_AfterUpdate()
[WorkOrderDescription] = StrConv([WorkOrderDescription], 3)
End Sub
Is there a code somewhere I could be directed too or is there some Iff Statement/case I could add to this code here .
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Check for more than one hyphen:
Dim v As Variant
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
Dim i As Integer
For i = 1 To UBound(v) - 1
If Left(v(i), 1) <> " " Then _
If Right(v(i - 1), 1) <> " " Then _
If Right(v(i), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
SuperStrConv = s
End Function
Private Sub TestSuperStrConv()
Debug.Print SuperStrConv("REPLACE 1400-P-411 GATE VALVE")
End Sub
That should capitalise any text between two hyphens where there is not a space before or after either of the hyphens.
It works for that example in the test. I don't think it would produce undesired results in other examples but you may want to try a variety.
The one thing problem with it I can think of is it's only looking for spaces round the hyphens. Perhaps it should be looking for other white space characters too. Perhaps there's various ways to adapt it to do that. The obvious way would be lots of ORs in the IFs for the other white space characters.
Hi VilaRestal,
Thanks for your Help.
Sorry - Public Functions I Haven't real used these before and I am not sure how to call this up. I need a little more Help.
I copy and pasted it into the form I am on.
I tied to call this on after update
Me![WorkOrderDescription]= SuperStrConv
It says complile error argument not optional
Tried
Me![WorkOrderDescription]= call SuperStrConv
It says complile syntax errror
Tried
call SuperStrConvIt says complile error argument not optional
Also don't know whay to do with this
Private Sub TestSuperStrConv()
Debug.Print SuperStrConv("REPLACE 1400-P-411 GATE VALVE")
End Sub
It's just a test to see if the function works as intended, and you see that there is a string argument between the perens, when you call TestSuperStrConv it prints out the result in the immediatewindow.
You do not need it so you can remove it for your project.
Ah -- I see.
I will remove it.
I am working on Adding some more If's for the Brackets. I will see if I can do it from the Code. I have a very good start.....(I may need more Help..)
Thanks for All your Help guys
Hi VilaRestal,
Thanks
I was just trying to add an If statement to your code for the brackets but now I see that a bracket could have a space inbetween. I am still a beginer level VBA.
Could you give me a bit more help of an example to Start a Uppercase after any "(" Bracket.
I have this now for your code but is not working I put a k instead of the V and used a Y instead if the i
Public Function SuperStrConv(ByVal s As String) As String
s = StrConv(s, vbProperCase)
Dim v As Variant
Dim k As Variant
v = Split(s, "-")
k = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 1 Then
Dim i As Integer
For i = 1 To UBound(v) - 1
If Left(v(i), 1) <> " " Then _
If Right(v(i - 1), 1) <> " " Then _
If Right(v(i), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
ElseIf IsArray(k) Then
If UBound(k) > 1 Then
Dim y As Integer
For y = 1 To UBound(k) - 1
If Left(k, 1) <> " " Then _
If Right(k(y - 1), 1) <> " " Then _
If Right(k, 1) <> " " Then _
If Left(k(y + 1), 1) <> " " Then _
k(i) = UCase(k)
Next y
s = k(0)
For y = 1 To UBound(k)
s = s & "(" & k
Next y
End If
End If
End If
SuperStrConv = s
End Function
This code is over my head as to what it all means but If I had more of an example I might be able to make it work...
to indent
[Code]
Public Function SuperStrConv(ByVal s As String) As String
s = StrConv(s, vbProperCase)
Dim v As Variant
Dim k As Variant
v = Split(s, "-")
k = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 1 Then
Dim i As Integer
For i = 1 To UBound(v) - 1
If Left(v(i), 1) <> " " Then _
If Right(v(i - 1), 1) <> " " Then _
If Right(v(i), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
ElseIf IsArray(k) Then
If UBound(k) > 1 Then
Dim y As Integer
For y = 1 To UBound(k) - 1
If Left(k(y), 1) <> " " Then _
If Right(k(y - 1), 1) <> " " Then _
If Right(k(y), 1) <> " " Then _
If Left(k(y + 1), 1) <> " " Then _
k(i) = UCase(k(y))
Next y
s = k(0)
For y = 1 To UBound(k)
s = s & "(" & k(y)
Next y
End If
End If
End If
SuperStrConv = s
End Function
To add the extra feature do it in sequence. Don't try to mix the two processes together and it's not either or, we want to process it for both hyphens and open brackets.
First we apply the StrConv, then to the result of that we process to capitalize the letters between hyphens, then to the result of that we capitalize the first letter after an open bracket:
Code:
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens and capitalizes first letter after an open bracket
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Part 1: Capitalize letters between hyphens without spaces
'Check for more than one hyphen:
Dim v As Variant
Dim i As Integer
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
For i = 1 To UBound(v) - 1
If Left(v(i), 1) <> " " Then _
If Right(v(i - 1), 1) <> " " Then _
If Right(v(i), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
'Part 2: Capitalize first letter after open brackets
v = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "(" & v(i)
Next i
End If
End If
SuperStrConv = s
End Function
It means we can reuse variables and keeps the structure clean and easy to debug and modify.
There are some not exactly advanced but beyond basic methods being used there. I don't have the time tonight to explain them (the split function and arrays and loops etc.). But having said that, if you start to learn VBA then understanding this function won't take long.
Thanks for All the Help!
The more I do this the more I will understand it....
I see - make the code in Blocks 1 by 1
I will try and add some more to it.
Private Sub TestSuperStrConv()
Debug.Print SuperStrConv("REPLACE 1400-P-411 GATE VALVE, REPLACE 2123-LV-007 BYPASS SPOOL VALVES (TRAIN 3), REPLACE 8300-FG-811 CHECK VALVE")
End Sub
shows it. The fix is to replace
Code:
If Left(v(i), 1) <> " " Then _
If Right(v(i - 1), 1) <> " " Then _
If Right(v(i), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
with
Code:
If InStr(v(i), " ") = 0 Then _
If Right(v(i - 1), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
so it's checking for any spaces anywhere between hyphens. And that's neater anyway, if perhaps a little less quick.
The only possible undesired behaviour I can see now (other than making acronyms lower case - NASA would become Nasa - which I don't see anyway round short of a dictionary of them to compare words to) is that phrases with multiple hyphens would get their middle words capitalized:
" mother-in-law "
will become
" Mother-IN-Law "
To get round that would be difficult. Perhaps you could only capitalize if numbers are present in either of the words outside the hyphen. But then the code does start to get quite complex and correspondingly less efficient.
So, as things stand, only use this function if those two issues don't matter.
Hi VilaRestal,
Thanks for that!
I changed it and I also added some extra into your code for some other common text that could be written in this textBox.
Here is what I did. I followed your example
Code:
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens and capitalizes first letter after an open bracket
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Part 1: Capitalize letters between hyphens without spaces
'Check for more than one hyphen:
Dim v As Variant
Dim i As Integer
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
For i = 1 To UBound(v) - 1
'If Left(v(i), 1) <> " " Then _
'If Right(v(i - 1), 1) <> " " Then _
'If Right(v(i), 1) <> " " Then _
'If Left(v(i + 1), 1) <> " " Then _
'v(i) = UCase(v(i))
If InStr(v(i), " ") = 0 Then _
If Right(v(i - 1), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
'Part 2: Capitalize first letter after open brackets
v = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "(" & v(i)
Next i
End If
End If
'Part 3: Capitalize FTS
v = Split(s, "FTS")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "FTS" & v(i)
Next i
End If
End If
'Part 4: Capitalize LP
v = Split(s, "LP")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing the letters:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & "LP" & v(i)
Next i
End If
End If
'Part 5: LowerCase for the word To
v = Split(s, " To")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element LowerCase first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = LCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & " to" & v(i)
Next i
End If
End If
'Part 6: LowerCase for the word In
v = Split(s, " In")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element LowerCase first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = LCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & " in" & v(i)
Next i
End If
End If
SuperStrConv = s
End Function
[\Code]
I hope I am on the right Track..
There was one thing I am not sure if you can do this is to Put Multi/Group of Text Where the v = Split(s, " To")
(Eg Like this: v = Split(s, " To" & "LP" & "FTS") - group instead of making a lot of if statements.
Thanks BBryan
Sorry I just saw the Code Tag Didn't work
Try again
Code:
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens and capitalizes first letter after an open bracket
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Part 1: Capitalize letters between hyphens without spaces
'Check for more than one hyphen:
Dim v As Variant
Dim i As Integer
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
For i = 1 To UBound(v) - 1
'If Left(v(i), 1) <> " " Then _
'If Right(v(i - 1), 1) <> " " Then _
'If Right(v(i), 1) <> " " Then _
'If Left(v(i + 1), 1) <> " " Then _
'v(i) = UCase(v(i))
If InStr(v(i), " ") = 0 Then _
If Right(v(i - 1), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
'Part 2: Capitalize first letter after open brackets
v = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "(" & v(i)
Next i
End If
End If
'Part 3: Capitalize FTS
v = Split(s, "FTS")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "FTS" & v(i)
Next i
End If
End If
'Part 4: Capitalize LP
v = Split(s, "LP")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing the letters:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & "LP" & v(i)
Next i
End If
End If
'Part 5: LowerCase for the word To
v = Split(s, " To")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element LowerCase first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = LCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & " to" & v(i)
Next i
End If
End If
'Part 6: LowerCase for the word In
v = Split(s, " In")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element LowerCase first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = LCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & " in" & v(i)
Next i
End If
End If
SuperStrConv = s
End Function[\Code]
Sorry
Sorry the Code Tag Didn't work
I see my Mistake wrong [\Code] I think it should be [/Code]
Sorry Try again
Code:
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens and capitalizes first letter after an open bracket
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Part 1: Capitalize letters between hyphens without spaces
'Check for more than one hyphen:
Dim v As Variant
Dim i As Integer
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
For i = 1 To UBound(v) - 1
'If Left(v(i), 1) <> " " Then _
'If Right(v(i - 1), 1) <> " " Then _
'If Right(v(i), 1) <> " " Then _
'If Left(v(i + 1), 1) <> " " Then _
'v(i) = UCase(v(i))
If InStr(v(i), " ") = 0 Then _
If Right(v(i - 1), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
'Part 2: Capitalize first letter after open brackets
v = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "(" & v(i)
Next i
End If
End If
'Part 3: Capitalize FTS
v = Split(s, "FTS")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "FTS" & v(i)
Next i
End If
End If
'Part 4: Capitalize LP
v = Split(s, "LP")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing the letters:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & "LP" & v(i)
Next i
End If
End If
'Part 5: LowerCase for the word To
v = Split(s, " To")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element LowerCase first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = LCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & " to" & v(i)
Next i
End If
End If
'Part 6: LowerCase for the word In
v = Split(s, " In")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element LowerCase first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = LCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert the Letters):
s = v(0)
For i = 1 To UBound(v)
s = s & " in" & v(i)
Next i
End If
End If
SuperStrConv = s
End Function
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens and capitalizes first letter after an open bracket
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Part 1: Capitalize letters between hyphens without spaces
'Check for more than one hyphen:
Dim v As Variant
Dim i As Integer
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
For i = 1 To UBound(v) - 1
If InStr(v(i), " ") = 0 Then _
If Right(v(i - 1), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
'Part 2: Capitalize first letter after open brackets
v = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "(" & v(i)
Next i
End If
End If
SuperStrConv = s
End Function
Split is used to make an array of strings out of a source string, splitting it into elements on the split string specified (splitting on hyphens or closed brackets or anything else). To just capitalise certain groups of letters you could find and replace the groups with the capitalised versions of themselves. Say for example:
s = Replace(s, "FTS", "FTS")
Which might look a bit bizarre but the string it's searching for (the first "FTS") isn't case sensitive so it will replace all instances of "fts" or "Fts" or "fTs" with "FTS". The problem is if "fts" can appear in the middle of any words such ("lifts" would become "liFTS" so we could only replace "fts" with spaces round it
s = Replace(s, " FTS ", " FTS ")
(so only fts by itself) but that would ignore " fts," and " fts.". Those cases would have to be handled separately:
s = Replace(s, " FTS ", " FTS ")
s = Replace(s, " FTS,", " FTS,")
s = Replace(s, " FTS.", " FTS.")
As we're going to do this for more than one 'word' it might be good to make a reusable function:
Code:
Private Function AllCaseEachOfWord(ByVal strIn As String, strWord As String, Optional ByVal blnUpper As Boolean = True) As String
Dim sU As String, sL As String
sU = UCase(strWord)
sL = LCase(strWord)
strIn = Replace(strIn, " " & strWord & " ", " " & IIf(blnUpper, sU, sL) & " ")
strIn = Replace(strIn, " " & strWord & ",", " " & IIf(blnUpper, sU, sL) & ",")
strIn = Replace(strIn, " " & strWord & ".", " " & IIf(blnUpper, sU, sL) & ".")
strIn = Replace(strIn, "(" & strWord & " ", "(" & IIf(blnUpper, sU, sL) & " ")
strIn = Replace(strIn, " " & strWord & ")", " " & IIf(blnUpper, sU, sL) & ")")
AllCaseEachOfWord = strIn
End Function
This could then simply be used:
s = AllCaseEachOfWord(s, "fts")
s = AllCaseEachOfWord(s, "lp")
to uppercase those words.
To lowercase the others you would use
s = AllCaseEachOfWord(s, "to", False)
s = AllCaseEachOfWord(s, "in", False)
s = AllCaseEachOfWord(s, "a", False)
but the problem is if the word is at the beginning of a sentance then it should keep it's capital first letter. Oh this sort of text formatting gets really tricky as you can see.
Anyway the code as it stands would be:
Code:
Public Function SuperStrConv(ByVal s As String) As String
'Works the same as StrConv(s, vbProperCase) except capitalises text between two hyphens without spaces either side of hyphens and capitalizes first letter after an open bracket
'Process as StrConv(s, vbProperCase):
s = StrConv(s, vbProperCase)
'Part 1: Capitalize letters between hyphens without spaces
'Check for more than one hyphen:
Dim v As Variant
Dim i As Integer
v = Split(s, "-")
If IsArray(v) Then
If UBound(v) > 1 Then
'Loop through inner elements and capitalise those that need it:
For i = 1 To UBound(v) - 1
If InStr(v(i), " ") = 0 Then _
If Right(v(i - 1), 1) <> " " Then _
If Left(v(i + 1), 1) <> " " Then _
v(i) = UCase(v(i))
Next i
'Rebuild the string from the array (remembering to reinsert hyphens):
s = v(0)
For i = 1 To UBound(v)
s = s & "-" & v(i)
Next i
End If
End If
'Part 2: Capitalize first letter after open brackets
v = Split(s, "(")
If IsArray(v) Then
If UBound(v) > 0 Then
'Loop through all but first element capitalizing first letter:
For i = 1 To UBound(v)
If Len(v(i)) > 0 Then v(i) = UCase(Left(v(i), 1)) & Right(v(i), Len(v(i)) - 1)
Next i
'Rebuild the string from the array (remembering to reinsert open brackets):
s = v(0)
For i = 1 To UBound(v)
s = s & "(" & v(i)
Next i
End If
End If
s = AllCaseEachOfWord(s, "fts")
s = AllCaseEachOfWord(s, "lp")
s = AllCaseEachOfWord(s, "to", False)
s = AllCaseEachOfWord(s, "in", False)
s = AllCaseEachOfWord(s, "a", False)
SuperStrConv = s
End Function
Private Function AllCaseEachOfWord(ByVal strIn As String, strWord As String, Optional ByVal blnUpper As Boolean = True) As String
Dim sU As String, sL As String
sU = UCase(strWord)
sL = LCase(strWord)
strIn = Replace(strIn, " " & strWord & " ", " " & IIf(blnUpper, sU, sL) & " ")
strIn = Replace(strIn, " " & strWord & ",", " " & IIf(blnUpper, sU, sL) & ",")
strIn = Replace(strIn, " " & strWord & ".", " " & IIf(blnUpper, sU, sL) & ".")
strIn = Replace(strIn, "(" & strWord & " ", "(" & IIf(blnUpper, sU, sL) & " ")
strIn = Replace(strIn, " " & strWord & ")", " " & IIf(blnUpper, sU, sL) & ")")
AllCaseEachOfWord = strIn
End Function
It could certainly be improved a bit. I'll have a think about that. Perhaps you can see the advantage of having created the AllCaseEachOfWord function: that can be refined in one place and ensures all the words we process for work the same way and get the same improvements.
And in answer to your last question: No, Split can't be used like that. You example would just equate to v = Split(s, " ToLPFTS"). And if it could it still wouldn't be the appropriate tool for this bit of the job as I hope you can now see.
Oh and btw, you can edit previous posts, you don't have to repost the code to put it in code brackets.
It would be good if you could edit the first so the code in it is in code brackets and edit the other two so they just say "deleted duplicate" or something like that.
, 1) <> " " Then _