Extracting Irregular Length String from Another Irregular Length String

[DenBis]

Good day,

The following string is a typical sample data found in textbox [txtProxy] of
my form (note there will never be a set fixed number of characters):

X400:c=CA;a=GOVMT.CANADA;p=MDD.DMM;o=Surrey;s=Bisson;g=Denis;i=DD;%SMTP:
(e-mail address removed)%GWISE: MDD.DMM.Surrey.BissonDD

From another textbox [txtEmail], and for this sample data, I want to be able
to extract '(e-mail address removed)' from [txtProxy].

Constants found in ALL data include “@uot.gc.ca†and “SMTP:†Essentially, I
want to be able to capture anything following the colon after “SMTP:†up to
and including “.caâ€

I assume this may require a combination of functions, however, this is a bit
too advanced for me.

Any help is appreciated.

Thank you

Denis

 

[Albert D. Kallal]

This worked for me:

Dim strI As String

strI =
"X400:c=CA;a=GOVMT.CANADA;p=MDD.DMM;o=Surrey;s=Bisson;g=Denis;i=DD;%SMTP:[email protected]%GWISE:
MDD.DMM.Surrey.BissonDD"

Debug.Print Split(Split(strI, ":")(2), "%")(0)

The results were:

(e-mail address removed)


So, is the number of ":" consistant? I count 3 in the above?

 

[DenBis via AccessMonster.com]

Hi Albert.

Thank you for your reply. In answering your question, no the number of ":" is
not consistent.

I am not familiar with the "split" function, hence I will read up on it and
try to apply it to my situation.

In your reply, you gave "str=" the value of my sample. Again, understanding
that this is only one of about 50,000 pieces of data, I suppose I would refer
"str" to the value of the field/text box.

Thanks again.

Denis

 

[Albert D. Kallal]

Hi Albert.

Thank you for your reply. In answering your question, no the number of ":"
is
not consistent.

Ok, so in place of the ":" delimiter, we will use "SMTP:" for the start.

Hence, we get:

Debug.Print Split(Split(strI, "SMTP:")(1), "%")(0)

For the end, I used a %, can we still use that? Or, must we use ".ca" ?

If you *must* use ca, and can't rely on %, then we could use

Debug.Print Split(Split(strI, "SMTP:")(1), ".ca")(0) & ".ca"

In your reply, you gave "str=" the value of my sample. Again,
understanding
that this is only one of about 50,000 pieces of data, I suppose I would
refer
"str" to the value of the field/text box.

Yes, you could use that expression in a update query to "update" a new blank
email field you make. Or, perhaps you do this in code that loops through the
whole table (a update query is easier - no code to write). The example I had
was *just* an example, but does give you that "hard" part of the expression
needed to pull out the email. You could actually use that query in a report,
or even a export routine. However, you might want to use a update query,a nd
thus "move" out that email to an actual new column that you define (so, a
update query would do that for you).

So, yes, replace strI with the field name if you use this expression in a
query...

 

[DenBis via AccessMonster.com]

Good day Albert,

Thank you for the clarification and useful assistance. I will be fiddling
with that first thing this morning.

Cheers

Denis

 

[DenBis via AccessMonster.com]

Hi Albert,

Just a (belated) update: Your solution worked like a charm.

Thanks again

Denis