By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,586 Members | 623 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,586 IT Pros & Developers. It's quick & easy.

Help needed in removing text from either side at the same time

P: n/a
Hi,

I have a table which contains records of user access and searches made
within an application, this is a sample of the data:

response for 101 results from database[LJI] in 28906 ms
I only want to keep the text contained in the brackets and nothing
else.

I have used the following expression but it only keeps text to the
right

Expression used:
DataSource2: Right([DataSource],Len([DataSource])-InStr([DataSource],"["))

Results obtained - LJI] in 28906 ms

Without having to do a similar expression to remove the other text is
there an expression I can use remove the text that is no required in
one step.

Can anyone help.

Thanks

Steve
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On 3 Dec 2003 05:53:53 -0800, mu******@hotmail.com (Steve) wrote:

You're on the right track, using InStr to find a specific character.
Also throw in the mix the Mid$ function, and you should be able to
create that expression.

-Tom.

Hi,

I have a table which contains records of user access and searches made
within an application, this is a sample of the data:

response for 101 results from database[LJI] in 28906 ms
I only want to keep the text contained in the brackets and nothing
else.

I have used the following expression but it only keeps text to the
right

Expression used:
DataSource2: Right([DataSource],Len([DataSource])-InStr([DataSource],"["))

Results obtained - LJI] in 28906 ms

Without having to do a similar expression to remove the other text is
there an expression I can use remove the text that is no required in
one step.

Can anyone help.

Thanks

Steve


Nov 12 '05 #2

P: n/a
What I think your looking for is the Mid() & Instr() functions. The first
question would be is the length of text inside the brackets always the same
length? Without knowing the answer to the last question we have to assume
not. Based on your example we will also assume our data is inside "[" and
"]" (which could be easily modified). Looks like alot of code below but its
really just comments. but the idea is to simply call this function as you
loop through your records.

usage ex:
do
...
MyData = GetDataInside(rs![DataSource])
...
loop

HTH

Mike Krous

Public Function GetDataInside(strRaw As String) As String
Dim intOpenBracPos As Integer 'location of open bracket
Dim intCloseBracPos As Integer 'location of close bracket
Dim strResult As String 'Final result after manipulations

'Find the first open bracket
intOpenBracPos = InStr(1, strRaw, "[", vbTextCompare)

'do we have an open bracket in here?
If intOpenBracPos > 0 Then
'yes we do, continue

'lets find our close bracket, use intOpenBracPos as our
'start point this time to make sure we get the first close bracket
'_after_ our open bracket.
intCloseBracPos = InStr(intOpenBracPos, strRaw, "]", vbTextCompare)

'did we find a close bracket after our open bracket?
If intCloseBracPos > 0 Then
'yes we did, things are lookin good, lets continue..

'ok, we have our start and stop point, lets simply get the data
inbetween

'first we gotta get the length between our start and stop point
'because Mid does not want a stop point but a length of
characters
'to get. the -1 at the end is removing the ] because remember,
instr
'gives us the position of the ] because thats what we searched
for
intResultLen = intCloseBracPos - intOpenBracPos - 1

'finally, lets get our data!
strResult = Mid(strRaw, intOpenBracPos + 1, intResultLen)
Else
'no close bracket found. lets just return from the open bracket
'we found to the end of the string, or we could return a blank
string
'but we'll try to get some data instead

'here we use the Mid() function, two things are useful to know
here
'first I pass intOpenBracPos+1 as my start position, that will
put us
'one character past the open bracket because the instr()
function
'gave use the exact location of the bracket.
'second, I do not supply a length parameter, this tells the mid
function
'to return the data to the end of the string.
strResult = Mid(strRaw, intOpenBracPos + 1)
End If
Else
'couldnt find open bracket, return empty string
strResult = ""
End If

GetDataInside = strResult
End Function


"Steve" <mu******@hotmail.com> wrote in message
news:3e**************************@posting.google.c om...
Hi,

I have a table which contains records of user access and searches made
within an application, this is a sample of the data:

response for 101 results from database[LJI] in 28906 ms
I only want to keep the text contained in the brackets and nothing
else.

I have used the following expression but it only keeps text to the
right

Expression used:
DataSource2: Right([DataSource],Len([DataSource])-InStr([DataSource],"["))

Results obtained - LJI] in 28906 ms

Without having to do a similar expression to remove the other text is
there an expression I can use remove the text that is no required in
one step.

Can anyone help.

Thanks

Steve

Nov 12 '05 #3

P: n/a
> Expression used:
DataSource2: Right([DataSource],Len([DataSource])-InStr([DataSource],"["))
your using this in a query not in code :) will still work...

DataSource2: GetDataInside([DataSource])

HTH

Mike Krous

"Mike Krous" <m.krous@nospam_comcast.net> wrote in message
news:9o********************@comcast.com... What I think your looking for is the Mid() & Instr() functions. The first
question would be is the length of text inside the brackets always the same length? Without knowing the answer to the last question we have to assume
not. Based on your example we will also assume our data is inside "[" and
"]" (which could be easily modified). Looks like alot of code below but its really just comments. but the idea is to simply call this function as you
loop through your records.

usage ex:
do
...
MyData = GetDataInside(rs![DataSource])
...
loop

HTH

Mike Krous

Public Function GetDataInside(strRaw As String) As String
Dim intOpenBracPos As Integer 'location of open bracket
Dim intCloseBracPos As Integer 'location of close bracket
Dim strResult As String 'Final result after manipulations

'Find the first open bracket
intOpenBracPos = InStr(1, strRaw, "[", vbTextCompare)

'do we have an open bracket in here?
If intOpenBracPos > 0 Then
'yes we do, continue

'lets find our close bracket, use intOpenBracPos as our
'start point this time to make sure we get the first close bracket
'_after_ our open bracket.
intCloseBracPos = InStr(intOpenBracPos, strRaw, "]", vbTextCompare)
'did we find a close bracket after our open bracket?
If intCloseBracPos > 0 Then
'yes we did, things are lookin good, lets continue..

'ok, we have our start and stop point, lets simply get the data inbetween

'first we gotta get the length between our start and stop point 'because Mid does not want a stop point but a length of
characters
'to get. the -1 at the end is removing the ] because remember,
instr
'gives us the position of the ] because thats what we searched
for
intResultLen = intCloseBracPos - intOpenBracPos - 1

'finally, lets get our data!
strResult = Mid(strRaw, intOpenBracPos + 1, intResultLen)
Else
'no close bracket found. lets just return from the open bracket 'we found to the end of the string, or we could return a blank
string
'but we'll try to get some data instead

'here we use the Mid() function, two things are useful to know
here
'first I pass intOpenBracPos+1 as my start position, that will
put us
'one character past the open bracket because the instr()
function
'gave use the exact location of the bracket.
'second, I do not supply a length parameter, this tells the mid function
'to return the data to the end of the string.
strResult = Mid(strRaw, intOpenBracPos + 1)
End If
Else
'couldnt find open bracket, return empty string
strResult = ""
End If

GetDataInside = strResult
End Function


"Steve" <mu******@hotmail.com> wrote in message
news:3e**************************@posting.google.c om...
Hi,

I have a table which contains records of user access and searches made
within an application, this is a sample of the data:

response for 101 results from database[LJI] in 28906 ms
I only want to keep the text contained in the brackets and nothing
else.

I have used the following expression but it only keeps text to the
right

Expression used:
DataSource2: Right([DataSource],Len([DataSource])-InStr([DataSource],"["))
Results obtained - LJI] in 28906 ms

Without having to do a similar expression to remove the other text is
there an expression I can use remove the text that is no required in
one step.

Can anyone help.

Thanks

Steve


Nov 12 '05 #4

P: n/a
DataSource2:Mid([DataSource], InStr([DataSource], "[") + 1,
InStr([DataSource], "]") - InStr([DataSource], "[") - 1)

Phil
"Steve" <mu******@hotmail.com> wrote in message
news:3e**************************@posting.google.c om...
Hi,

I have a table which contains records of user access and searches made
within an application, this is a sample of the data:

response for 101 results from database[LJI] in 28906 ms
I only want to keep the text contained in the brackets and nothing
else.

I have used the following expression but it only keeps text to the
right

Expression used:
DataSource2: Right([DataSource],Len([DataSource])-InStr([DataSource],"["))

Results obtained - LJI] in 28906 ms

Without having to do a similar expression to remove the other text is
there an expression I can use remove the text that is no required in
one step.

Can anyone help.

Thanks

Steve

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.