Cap 1

[Finedean]

Hi All,
How can change the following words:

N.s.u.h. @ Plainview

To:

N.S.U.H. @ Plainview

Thanks in advance

Dean

 

[SkipVought]

In what application?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Finedean]

in a microsoft access report.
thanks

 

[PHV]

You may use a function like this:
Public Function myProperCase(strInput)
Dim i, x, strOut, flg
flg = True
For i = 1 To Len(strInput)
x = LCase(Mid(strInput, i, 1))
If Not IsNumeric(x) And (x < "a" Or x > "z") Then
flg = True
ElseIf flg Then
x = UCase(x)
flg = False
End If
strOut = strOut & x
Next
myProperCase = strOut
End Function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Finedean]

Thank you. I'll try it.

Dean

 

[TonyJollans]

PH is usually pretty good with this kind of thing so I won't be surprised if his function does what you want.

If not, what are the criteria for upper-casing?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[rmikesmith]

I interpreted Dean's request as wanting to capitalize only the text characters before the @ symbol (in this case), as depicted in his before & after example. If that is the case, here is a general function that will capitalize only the characters up to a specific character, but not beyond:

Function CapitalizeBeforeToken(ByVal sInText As String, sTokenChar As String) As String
Dim sOutText As String
Dim sUnchangedText As String
Dim Pos As Long

   CapitalizeBeforeToken = sInText
   If Len(sTokenChar) <> 1 Then
     CapitalizeBeforeToken = sInText
     Exit Function
   End If
   Pos = InStr(1, sInText, sTokenChar, vbTextCompare)
   If Pos > 1 Then
     sUnchangedText = Mid$(sInText, Pos)
     sOutText = Left$(sInText, Pos - 1)
     sOutText = UCase(sOutText)
     sOutText = sOutText & sUnchangedText
     CapitalizeBeforeToken = sOutText
   End If
End Function

Example usage:

Sub TestCap()
Dim sResult As String

   sResult = CapitalizeBeforeToken("N.s.u.h.@Plainview", "@")
   MsgBox "Converted Text = " & sResult
End Sub

If more than one token characters exist in the string, capitalization will occur only up to the first occurrence.


Regards,
Mike

 

[Finedean]

Hi All,
PHV's code worked.
thanks so much for helping.