Some sort of Trim function and how to get ALL CAPS changed..

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a projectname field that can have this sort of format:

SEARS- WESTMINSTER CO
or

SEARS - COLORADO SPRINGS, CO
or

SEARS CO SPRINGS
or

WINTER PARK, FL

What I'd like to do is Trim off each occurrence of "SEARS" along with that
dash that follows it (if applicable) AND convert ProjectName from all caps to
First letter of each word Capital (excluding the state obviously), and the
remaining letters as Lower case.

Any help on this would be appreciated!

Thank you,
MN
 
What do you want to happen to
SEARS CO SPRINGS

1) CO Springs
2) Co Springs
3) Colorado Springs

?
 
Here is an example of what I want to have happen to that particular example
you have listed:

I want SEARS CO SPRINGS to appearthis way:
CO Springs

Other Examples:
1) SEARS- WESTMINSTER CO
I want it to appear this way: Westminster CO

2) SEARS - COLORADO SPRINGS, CO
I want it to appear this way: Colorado Springs, CO

3) WINTER PARK, FL
I want it to appear this way: Winter Park, FL

Is this possible? What do I need to do in order to achieve this?

Thanks,
MN
 
SEARS-frankie
len = 13
text we want = 7

ok, so break it down into chunks:

First, we want to find if there is a "-" character and what position it
starts at:

FunctionA: Nz(InStr(1,"YOUR FIELD","-"),0)

This will return either 0 (if the "-" character is not found), or a number
representing it's position

Then we need to remove all the text before and including this

FunctionB: Right("YOUR FIELD",Len("YOUR FIELD")-FunctionA)

We also need to check if there was a trailing space after the "-"
character, like so:

FunctionC: LTrim$(FunctionB)


So, combining all these functions into one you will get:

LTrim$ (Right ("YOUR FIELD", Len ("YOUR FIELD") - Nz (InStr (1,"YOUR
FIELD","-"),0)))

I think that should work anyway, and you can see the logic of it, so if I
have missed anything you should be able to correct it easily enough.

Have any more probs with it post back and I'll take a closer look.

Cheers

John Webb
 
oops, almost forgot to convert to proper case:

StrConv ("YOUR NEW STRING",vbProperCase)

Which would then make your function look like:

StrConv (LTrim$ (Right ("YOUR FIELD", Len ("YOUR FIELD") - Nz (InStr
(1,"YOUR FIELD","-"),0))),vbProperCase)


Cheers

John Webb
 
With regards to keeping the state in uppercase - well, if the state is
ALWAYS listed, and is ALWAYS listed as 2 characters in length, then simply
take your existing string, shorten it by two characters then append the
last 2 characters of it to itself but using strConv("YOUR STRING",
vbUpperCase).

However, I would seriously suggest removing state altogether, and placing
it into a separate field.
 
Can be done, but it's not simple. I've got to set off to my day job now,
but will try and pull together some suggestions when i get home.
 
Works pretty well, however, the ones that DO NOT have a "-" character in the
string aren't getting rid of the "Sears" at the beginning like I would like
as well...any ideas?

Thanks!
 
The best way to tackle something like this - matching odd bits of text - is
usually to use regular expressions. Here's a little VBA function that uses
the VBScript regular expression object to find the four parts of your
field - Sears, the bit before the state, the state, and anything after the
state and process two of them. It works in an Access query. You need to test
it and maybe modify it to be sure it will work with all your data.

'*********** Start of code ***********************
Function DeSearize(V As Variant) As Variant

'Pattern to match US state abbreviations
Const rgxSTATES_US = "(A[KLRZ]|C[AOT]|D[CE]|FL|" _
& "GA|HI|I[ADLN]|K[SY]|LA|M[ADEINOST]|N[CDEHJMVY]|" _
& "O[HKR]|P[AR]|RI|S[CD]|T[NX]|" _
& "UT|V[AIT]|W[AIVY])"
Dim rgxEngine As Object
Dim rgxMatches As Object

Dim strStart As String
Dim strState As String
Dim strEnd As String

If IsNull(V) Then Exit Function

Set rgxEngine = CreateObject("VBScript.Regexp")
rgxEngine.Pattern = "^(SEARS\W+)?(.*?)\b" _
& rgxSTATES_US _
& "\b(.*?)$"
rgxEngine.IgnoreCase = True
Set rgxMatches = rgxEngine.Execute(V)

If rgxMatches.Count > 0 Then
strStart = rgxMatches(0).Submatches(1)
strState = rgxMatches(0).Submatches(2)
strEnd = rgxMatches(0).Submatches(3)

strStart = StrConv(strStart, vbProperCase)
strEnd = StrConv(strEnd, vbProperCase)

DeSearize = strStart & strState & strEnd

Else 'field didn't match pattern, return unchanged
DeSearize = V
End If

Set rgxEngine = Nothing

End Function
'*************************end of code
 
John,

Great solution!

I'm familiar with regular expression (as I used to use a lot of vbscript),
and must admit that this way of dealing with it never entered my mind.
Definitely props to you, as this has quite possibly also resulted in the
solving a problem I encountered last week!
 
Thank you so much for that code - I am going to try it when I have a chance!!

Thanks again,
Steph

John Nurick said:
The best way to tackle something like this - matching odd bits of text - is
usually to use regular expressions. Here's a little VBA function that uses
the VBScript regular expression object to find the four parts of your
field - Sears, the bit before the state, the state, and anything after the
state and process two of them. It works in an Access query. You need to test
it and maybe modify it to be sure it will work with all your data.

'*********** Start of code ***********************
Function DeSearize(V As Variant) As Variant

'Pattern to match US state abbreviations
Const rgxSTATES_US = "(A[KLRZ]|C[AOT]|D[CE]|FL|" _
& "GA|HI|I[ADLN]|K[SY]|LA|M[ADEINOST]|N[CDEHJMVY]|" _
& "O[HKR]|P[AR]|RI|S[CD]|T[NX]|" _
& "UT|V[AIT]|W[AIVY])"
Dim rgxEngine As Object
Dim rgxMatches As Object

Dim strStart As String
Dim strState As String
Dim strEnd As String

If IsNull(V) Then Exit Function

Set rgxEngine = CreateObject("VBScript.Regexp")
rgxEngine.Pattern = "^(SEARS\W+)?(.*?)\b" _
& rgxSTATES_US _
& "\b(.*?)$"
rgxEngine.IgnoreCase = True
Set rgxMatches = rgxEngine.Execute(V)

If rgxMatches.Count > 0 Then
strStart = rgxMatches(0).Submatches(1)
strState = rgxMatches(0).Submatches(2)
strEnd = rgxMatches(0).Submatches(3)

strStart = StrConv(strStart, vbProperCase)
strEnd = StrConv(strEnd, vbProperCase)

DeSearize = strStart & strState & strEnd

Else 'field didn't match pattern, return unchanged
DeSearize = V
End If

Set rgxEngine = Nothing

End Function
'*************************end of code
Click to expand...
 
Back
Top