separating the characters in a string

  • Thread starter Thread starter Erik
  • Start date Start date
E

Erik

I want to compare to fields from a table. The problem is
that one of my fields will have "extra" data that I do not
need or want for the comparison. Is it possible to use VB
to look at the string and assign only portion of the
string I want to a variable.

Example

XyzABCCOMPANYcba

I want a function that will look at the field find the
above example, but when the variable is defined I only
want ABCCOMPANY.
 
Erik,
Depends on what rules were used to create the XyzABCCOMPANYcba entry.
Let's call that field [CompanyCode] just for example.

Are there always just 3 chars before and 3 chars after the company name?
If so, then...
=Mid(CompanyCode,4,Len(CompanyCode)-6)
would yield "ABCCOMPANY"

Or... in your example, can you always find the "z", and always find the
"c" (the character just before the real name, and the character just after
the real name)
If so, then...

=Mid([CompanyCode],InStr([CompanyCode],"z")+1,InStr([CompanyCode],"c")-InStr
([CompanyCode],"z")-1)
should also yield "ABCCOMPANY"
 
Back
Top