Need to extract data mid-string.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KFox - 26 Mar 2007 22:19 GMT

Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
And here's what I'm trying to get back: Swissco\Default.htm

The number of characters from the left will always be the same, 28. And the
string will always be trimmed after the ":" (colon).

I'm struggling with how to use mid, left, right to get out what I need.

Thanks in advance!
Kellie

Reply to this Message

fredg - 26 Mar 2007 22:49 GMT

> Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
> And here's what I'm trying to get back: Swissco\Default.htm
[quoted text clipped - 6 lines]
> Thanks in advance!
> Kellie

Do it in 2 steps in a user defined function.

=Mid("\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754",29) Will
return
"Swissco\Default.htm: 754"

Then
Left("Swissco\Default.htm: 754",InStr("Swissco\Default.htm:
754",":")-1) Will return
"Swissco\Default.htm"

In a Module,:

Function ShortenString(StringIn as String) as String
Dim strNew as string
strNew = Mid(StringIn,29)
strNew = Left(strNew,InSgtr(strNew,":")-1)

ShortenString = strNew
End Function
==========

Then call it from a query:
NewColumn:ShortenString([FieldName])

or in a report or form:
= ShortenString([FieldName])

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

KFox - 27 Mar 2007 13:51 GMT

Wow! Thank you!

I'd like to do the user defined function, but neglected to mention that the
text "\\lavaur4\PHIWEBEurope\SWAN" is different in each record. The only
thing that's constant is it's always 28 characters long.

For example:
\\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
\\labaoo3\PHIWEBNASbb\WIRT_Match\Default.htm: 766

So, from those two records, I need to return:
Swissco\Default.htm
Match\Default.htm

Kellie

>> Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
>> And here's what I'm trying to get back: Swissco\Default.htm
[quoted text clipped - 29 lines]
>or in a report or form:
>= ShortenString([FieldName])

Reply to this Message

KFox - 27 Mar 2007 14:11 GMT

Actually, I figured it out. This is what I used:

Step1: Mid([FieldName],InStr([FieldName],"_")+1,InStr([FieldName],":")-InStr(
[FieldName],"_")-1)

Thanks again for your help!!

>Wow! Thank you!
>
[quoted text clipped - 17 lines]
>>or in a report or form:
>>= ShortenString([FieldName])

Reply to this Message

Ofer Cohen - 28 Mar 2007 02:17 GMT

Try

Mid([FieldName],29,instr([FieldName],":")-29)

Signature

Good Luck
BS"D

> Here's what I have: \\lavaur4\PHIWEBEurope\SWAN_Swissco\Default.htm: 754
> And here's what I'm trying to get back: Swissco\Default.htm
[quoted text clipped - 6 lines]
> Thanks in advance!
> Kellie

Reply to this Message

KFox - 29 Mar 2007 14:07 GMT

Thanks Ofer-- I'm going to write that down for future reference. No doubt,
this may come up again. :)

Kellie

>Try
>
[quoted text clipped - 5 lines]
>> Thanks in advance!
>> Kellie

Reply to this Message