vbscript help change lower case to upper

T

trafficzombie

Board Regular
Joined
Sep 9, 2011
Messages
63
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:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Doesn't it already do that ( according to that code )?
 
Upvote 0
It accepts lower case, which is what I thought you meant. Do you mean that you want instead a function that returns strings?
 
Upvote 0
GlennUK said:
It accepts lower case, which is what I thought you meant. Do you mean that you want instead a function that returns strings?
Click to expand...

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
 
Upvote 0
bluefeather8989 said:
plz go to http://www.mrexcel.com/forum/misc.php?do=bbcode#code
and then copy and past your code from excel:)
Click to expand...

Rich (BB 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
 
Upvote 0
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?
 
Upvote 0
GlennUK said:
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?
Click to expand...

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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Worksheet_Change when user Drags File Handle or Copy/Paste Multiple
Replies
3
Views
389
Joe4
Joe4
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
175
myall_blues
myall_blues
K
  • Solved
Combining 3 Worksheet_Change events
Replies
4
Views
274
Kevo
K
J
  • Locked
  • Question
Vlookup from multiselect data validation list
Replies
2
Views
722
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,390
Messages
6,119,235
Members
448,879
Latest member
VanGirl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top