Strip Text to Right and left of words 3

[Zeroanarchy]

Hi,

I have a table with a memo field in it. The memo field contains a lot of data with squares between set values.

I am interested to know If there is a method of stripping data to the left of a word and to the right of a word.

Example.
(1)
Your Cover Option: Package Cover: Plan 1
Membership Type: Single Government

From this example: I am after the words Plan 1 and Single

(2)
34544 Your Cover Option: Package Cover: Choice 1
Membership Type: Family Government

From this example: I am after the words Choice 1 and Family

Any ideas are appreciated.

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[SgtJarrow]

Sure you can...You need to look at the following Built-In Access functions:

Left
Right
Mid
InStr

These functions allow you to get sections of text as you define. The help files on each of these are pretty good. Take a look at them and if you are still stuck, come back here and tell us what you have tried and we will help you figure it out.

Just a couple pointers to help you...

With Your Cover Option: Package Cover: Plan 1
To get Plan 1

You will have to look closely at your logic. Do you want everything after the second colon )?? Or will it always be Plan 1, Plan 2, etc?? If thit is always the same length at the end, the Right function will work perfectly.

With Membership Type: Single Government
To get Single

Again, logic comes into play. Is the formatting always the same?? i.e. If it is always Membership Type: <i> some type </i> - the Mid or Left functions will work well.

Good luck and let us know how it goes.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[groleau]

Another option might be to use VBA:

function New_Trim (Item as string) as string

   NewTrim = Replace(Item, "", "")

end function

Cut the chunk of squares and paste them into wordpad.
save as text, and examine with a hex editor.

If they are all the same, paste one of them into the first
pair of quotes.

If the whole sequence is the same on every row, paste one sequence into that pair.

If they are 0A 0D (hex), replace the quotes with vbCrLf

If there are a lot of different characters, then you'll need an Item = Replace(Item, "X", "") for each distinct character (where X is the character).


--
Wes Groleau

http://www.Parkview.com/

 

[SkipVought]

Hi,

I'd make the following function...

Function Stripper(s As String) As String
    Dim a
    a = Split(s, ":")
    If UBound(a) = -1 Then
        Stripper = ""
    ElseIf UCase(Right(a(0), 4)) = "TYPE" Then
        Stripper = Split(a(1), " ")(1)
    ElseIf UBound(a) <= 1 Then
        Stripper = ""
    Else
        Stripper = Trim(a(2))
    End If
End Function

Skip,

_[red][/red]

 

[Zeroanarchy]

Thanks guys fantastic solutions, worked well.

Cheers


ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.