Page 1 of 3 123 LastLast
Results 1 to 10 of 25

vbscript help change lower case to upper

This is a discussion on vbscript help change lower case to upper within the Excel Questions forums, part of the Question Forums category; Hi I have the following code to check for valid post code formats and it works perfectly But I need ...

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    56

    Default vbscript help change lower case to upper

    Hi

    I have the following code to check for valid post code formats and it works perfectly

    But I need it to change any letter that is in lower case to upper...any help please?

    Thanks

    Public Function IsUKPostCode(strInput As String)
    'Uses a regular expression to validate the format of a postcode.
    'May require WindowsScripting 5.6 - downloadable from microsoft.com
    Dim RgExp As Variant
    'Create the regular expression object
    Set RgExp = CreateObject("VBScript.RegExp")
    'Clear the function value
    IsUKPostCode = ""
    'Check we have value to test
    If strInput = "" Then
    IsUKPostCode = "Not Supplied"
    Exit Function
    End If
    strInput = UCase(strInput)
    'This is the ridiculously complex expression that validates the postcode
    RgExp.Pattern = "(??:A[BL]|B[ABDHLNRST]?|" _
    & "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
    & "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
    & "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
    & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
    & "\d(?:\d|[A-Z])? \d[A-Z]{2})"
    'Does the fed in string match the pattern?
    If RgExp.test(strInput) = True And Len(strInput) <= 8 Then
    IsUKPostCode = "Valid"
    Else
    '------------------------------
    'Try to make a correct postcode
    '------------------------------
    'Despace & uppercase
    strInput = UCase(Replace(strInput, " ", ""))
    'Clean out any redundant characters - whilst most of these don't make sense
    'I've seen them all in postcodes before!
    strInput = Replace(strInput, "_", "")
    strInput = Replace(strInput, ",", "")
    strInput = Replace(strInput, "+", "")
    strInput = Replace(strInput, "-", "")
    strInput = Replace(strInput, ":", "")
    strInput = Replace(strInput, "=", "")
    strInput = Replace(strInput, "/", "")
    strInput = Replace(strInput, "*", "")
    strInput = Replace(strInput, "?", "")
    strInput = Replace(strInput, ".", "")
    '---------------------------------------------------------------------------
    'Check the string length again to make sure we've not got a "???" type entry
    '---------------------------------------------------------------------------
    If Len(strInput) = 0 Then
    IsUKPostCode = "Not Supplied"
    Exit Function
    ElseIf IsNumeric(strInput) Then
    IsUKPostCode = "All Numbers"
    Exit Function
    ElseIf Len(strInput) < 5 Then
    IsUKPostCode = "Too Short"
    Exit Function
    ElseIf Len(strInput) > 8 Then
    IsUKPostCode = "Too Long"
    Exit Function
    End If
    'Check for and correct substituted O (alpha) for 0 (numeric) at position len - 2
    If Mid(strInput, Len(strInput) - 2, 1) = "O" Then strInput = _
    Left(strInput, Len(strInput) - 3) & "0" & Right(strInput, 2)
    'Check for and correct substituted 0 (numeric) for O (alpha) at position 1 or 2
    If Mid(strInput, 2, 1) = "0" Then strInput = _
    Left(strInput, 1) & "O" & Right(strInput, Len(strInput) - 2)
    If Left(strInput, 1) = "0" Then strInput = _
    "O" & Right(strInput, Len(strInput) - 1)
    'Check for and correct substituted lowercase l for 1 at position len - 2
    If Mid(strInput, Len(strInput) - 2, 1) = "l" Then strInput = _
    Left(strInput, Len(strInput) - 3) & "1" & Right(strInput, 2)
    'Check for and correct substituted lowercase l for 1 at position 3
    If Mid(strInput, 3, 1) = "l" Then strInput = _
    Left(strInput, 2) & "1" & Right(strInput, Len(strInput) - 3)
    'Check for and correct substituted S for 5 at position len - 3
    If Mid(strInput, Len(strInput) - 3, 1) = "S" Then strInput = _
    Left(strInput, Len(strInput) - 3) & "5" & Right(strInput, 2)
    'Two possible lengths for a valid UK postcode
    Select Case Len(strInput)
    Case 6
    If RgExp.test(Left(strInput, 3) & " " & Right(strInput, 3)) = True Then
    'Format should be ?## #?? or ??# #??
    IsUKPostCode = Left(strInput, 3) & " " & Right(strInput, 3)
    Else
    IsUKPostCode = "Invalid"
    End If
    Case 7
    If RgExp.test(Left(strInput, 4) & " " & Right(strInput, 3)) = True Then
    'Format is ??## #?? or ?#?# #??
    IsUKPostCode = Left(strInput, 4) & " " & Right(strInput, 3)
    Else
    IsUKPostCode = "Invalid"
    End If
    Case Else
    IsUKPostCode = "Invalid"
    End Select
    End If
    End Function
    Last edited by trafficzombie; Oct 14th, 2011 at 09:57 AM.

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: vbscript help change lower case to upper

    Doesn't it already do that ( according to that code )?
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  3. #3
    Board Regular
    Join Date
    Sep 2011
    Posts
    56

    Default Re: vbscript help change lower case to upper

    Quote Originally Posted by GlennUK View Post
    Doesn't it already do that ( according to that code )?
    I thought that, but just checked it and it doesn't

    It just shows as valid rather than changing it to upper case !!

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: vbscript help change lower case to upper

    It accepts lower case, which is what I thought you meant. Do you mean that you want instead a function that returns strings?
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    56

    Default Re: vbscript help change lower case to upper

    Quote Originally Posted by GlennUK View Post
    It accepts lower case, which is what I thought you meant. Do you mean that you want instead a function that returns strings?
    Yeah...

    If there is any letter in lower case it changes them to upper case

    For example a post code that is sk12 3rt it changes it SK12 3RT

  6. #6
    Board Regular bluefeather8989's Avatar
    Join Date
    Nov 2009
    Posts
    171

    Default Re: vbscript help change lower case to upper

    plz go to http://www.mrexcel.com/forum/misc.php?do=bbcode#code
    and then copy and past your code from excel

  7. #7
    Board Regular
    Join Date
    Sep 2011
    Posts
    56

    Default Re: vbscript help change lower case to upper

    Quote Originally Posted by bluefeather8989 View Post
    plz go to http://www.mrexcel.com/forum/misc.php?do=bbcode#code
    and then copy and past your code from excel
    Code:
    Public Function IsUKPostCode(strInput As String)
    'Uses a regular expression to validate the format of a postcode.
    'May require WindowsScripting 5.6 - downloadable from microsoft.com
    Dim RgExp As Variant
    'Create the regular expression object
    Set RgExp = CreateObject("VBScript.RegExp")
    'Clear the function value
    IsUKPostCode = ""
    'Check we have value to test
    If strInput = "" Then
    IsUKPostCode = "Not Supplied"
    Exit Function
    End If
    strInput = UCase(strInput)
    'This is the ridiculously complex expression that validates the postcode
    RgExp.Pattern = "(?:(?:A[BL]|B[ABDHLNRST]?|" _
    & "C[ABFHMORTVW]|D[ADEGHLNTY]|E[CHNX]?|F[KY]|G[LUY]?|" _
    & "H[ADGPRSUX]|I[GMPV]|JE|K[ATWY]|L[ADELNSU]?|M[EKL]?|" _
    & "N[EGNPRW]?|O[LX]|P[AEHLOR]|R[GHM]|S[AEGKLMNOPRSTWY]?|" _
    & "T[ADFNQRSW]|UB|W[ACDFNRSV]?|YO|ZE)" _
    & "\d(?:\d|[A-Z])? \d[A-Z]{2})"
    'Does the fed in string match the pattern?
    If RgExp.test(strInput) = True And Len(strInput) <= 8 Then
    IsUKPostCode = "Valid"
    Else
    '------------------------------
    'Try to make a correct postcode
    '------------------------------
    'Despace & uppercase
    strInput = UCase(Replace(strInput, " ", ""))
    'Clean out any redundant characters - whilst most of these don't make sense
    'I've seen them all in postcodes before!
    strInput = Replace(strInput, "_", "")
    strInput = Replace(strInput, ",", "")
    strInput = Replace(strInput, "+", "")
    strInput = Replace(strInput, "-", "")
    strInput = Replace(strInput, ":", "")
    strInput = Replace(strInput, "=", "")
    strInput = Replace(strInput, "/", "")
    strInput = Replace(strInput, "*", "")
    strInput = Replace(strInput, "?", "")
    strInput = Replace(strInput, ".", "")
    '---------------------------------------------------------------------------
    'Check the string length again to make sure we've not got a "???" type entry
    '---------------------------------------------------------------------------
           If Len(strInput) = 0 Then
           IsUKPostCode = "Not Supplied"
           Exit Function
           ElseIf IsNumeric(strInput) Then
           IsUKPostCode = "All Numbers"
           Exit Function
           ElseIf Len(strInput) < 5 Then
           IsUKPostCode = "Too Short"
           Exit Function
           ElseIf Len(strInput) > 8 Then
           IsUKPostCode = "Too Long"
           Exit Function
           End If
           'Check for and correct substituted O (alpha) for 0 (numeric) at position len - 2
           If Mid(strInput, Len(strInput) - 2, 1) = "O" Then strInput = _
           Left(strInput, Len(strInput) - 3) & "0" & Right(strInput, 2)
           'Check for and correct substituted 0 (numeric) for O (alpha) at position 1 or 2
           If Mid(strInput, 2, 1) = "0" Then strInput = _
           Left(strInput, 1) & "O" & Right(strInput, Len(strInput) - 2)
           If Left(strInput, 1) = "0" Then strInput = _
           "O" & Right(strInput, Len(strInput) - 1)
           'Check for and correct substituted lowercase l for 1 at position len - 2
           If Mid(strInput, Len(strInput) - 2, 1) = "l" Then strInput = _
           Left(strInput, Len(strInput) - 3) & "1" & Right(strInput, 2)
           'Check for and correct substituted lowercase l for 1 at position 3
           If Mid(strInput, 3, 1) = "l" Then strInput = _
           Left(strInput, 2) & "1" & Right(strInput, Len(strInput) - 3)
           'Check for and correct substituted S for 5 at position len - 3
           If Mid(strInput, Len(strInput) - 3, 1) = "S" Then strInput = _
           Left(strInput, Len(strInput) - 3) & "5" & Right(strInput, 2)
           'Two possible lengths for a valid UK postcode
           Select Case Len(strInput)
           Case 6
           If RgExp.test(Left(strInput, 3) & " " & Right(strInput, 3)) = True Then
           'Format should be ?## #?? or ??# #??
           IsUKPostCode = Left(strInput, 3) & " " & Right(strInput, 3)
           Else
           IsUKPostCode = "Invalid"
           End If
           Case 7
           If RgExp.test(Left(strInput, 4) & " " & Right(strInput, 3)) = True Then
           'Format is ??## #?? or ?#?# #??
           IsUKPostCode = Left(strInput, 4) & " " & Right(strInput, 3)
           Else
           IsUKPostCode = "Invalid"
           End If
           Case Else
           IsUKPostCode = "Invalid"
           End Select
           End If
           End Function

  8. #8
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: vbscript help change lower case to upper

    Your current function returns TRUE/FALSE ... what would you like it to return instead? I know you've said that you want it to return an uppercase postcode, but what about when the postcode is invalid, what do you want returned then?
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

  9. #9
    Board Regular
    Join Date
    Sep 2011
    Posts
    56

    Default Re: vbscript help change lower case to upper

    Quote Originally Posted by GlennUK View Post
    Your current function returns TRUE/FALSE ... what would you like it to return instead? I know you've said that you want it to return an uppercase postcode, but what about when the postcode is invalid, what do you want returned then?
    thanks for your help Glenn

    Here is a brief of what I need this script to do

    * change all lower case to upper
    * If the post code format is valid (always has the last part as 3 digits, and the first part as either 2, 3 or 4, it shows as VALID (it currently does this)
    * If a post code has no gap it changes the code to having a gap (again it currently does this)
    * if there is any other symbol at the end of the post code like a . or anything other it removes it (it does this for all apart from 5 digit codes - but this is not the end of the world but would be handy)
    * More than one space between it changes to 1 space (which it does)
    * Not enough digits it shows as INVLAID (which it does)

    Nothing ever shows as TRUE/FLASE

    Hope this explains

  10. #10
    Board Regular
    Join Date
    Jul 2002
    Location
    Milton Keynes, England
    Posts
    10,945

    Default Re: vbscript help change lower case to upper

    Your requirements clash:
    this: * change all lower case to upper
    indicates that you want a postcode returned

    but this:
    * If the post code format is valid (always has the last part as 3 digits, and the first part as either 2, 3 or 4, it shows as VALID (it currently does this)
    indicates that you want the word VALID returned.

    You can't have both things happening.
    Cheers, Glenn.

    Beauty is in the eye of the beer-holder.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com