Parse one field's data into multiple fields

[jlicciardi]

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

Please help

[Darryl Kerkeslager]

On 21 Feb 2005 12:51:05 -0800, <jl********@mss ystems.com> wrote:

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

You may not want to use Right() if your field has 'irregular' data like
your first one above. You should use the Mid() function. Does your third
data segment always begin at the 13th position ... or is the best you can
say that it is always after the second semi-colon?

Before you answer that, ask yourself, is there any way to NOT put this
data in the same field to begin with? It looks like it should be in three
separate fields. Second, is there any way to eliminate irregular data,
which would certainly make it easier to extract.
Darryl Kerkeslager

[Darryl Kerkeslager]

On 21 Feb 2005 12:51:05 -0800, <jl********@mss ystems.com> wrote:

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

You may not want to use Right() if your field has 'irregular' data like
your first one above. You should use the Mid() function. Does your third
data segment always begin at the 13th position ... or is the best you can
say that it is always after the second semi-colon?

Before you answer that, ask yourself, is there any way to NOT put this
data in the same field to begin with? It looks like it should be in three
separate fields. Second, is there any way to eliminate irregular data,
which would certainly make it easier to extract.
Darryl Kerkeslager

[pietlinden]

jliccia...@mssy stems.com wrote:

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

Please help

Hope this doesn't have to be fast... I could do it with strReverse and
then snip off the first semi-colon and then take everything between
position 1 and the next semi-colon, but that was way too much of a
headache. Will this work for ya?

Option Compare Database

Public Function LastChunkOfStri ng(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563 550;
'returns: 563550
' 13767;38355;520 270-1;
'returns: 520270-1
' 44795;38355;110 818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfStri ng = ChunkString(UBo und(ChunkString ) - 1)

End Function
so I _guess_ you could do it in SQL...

SELECT... ChunkString(UBo und(Split(strTe xt, strDelim, , vbTextCompare))
- 1)
FROM...
WHERE...

but I didn't mess with that... that's the fun you get to have!

BTW, I agree with Darryl - the best way out of this mess in the future
is to not get yourself into it in the first place. Break up your data
as small as you can BEFORE you enter it, and you won't have to do these
things to begin with.

[pietlinden]

jliccia...@mssy stems.com wrote:

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

Please help

Hope this doesn't have to be fast... I could do it with strReverse and
then snip off the first semi-colon and then take everything between
position 1 and the next semi-colon, but that was way too much of a
headache. Will this work for ya?

Option Compare Database

Public Function LastChunkOfStri ng(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563 550;
'returns: 563550
' 13767;38355;520 270-1;
'returns: 520270-1
' 44795;38355;110 818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfStri ng = ChunkString(UBo und(ChunkString ) - 1)

End Function
so I _guess_ you could do it in SQL...

SELECT... ChunkString(UBo und(Split(strTe xt, strDelim, , vbTextCompare))
- 1)
FROM...
WHERE...

but I didn't mess with that... that's the fun you get to have!

BTW, I agree with Darryl - the best way out of this mess in the future
is to not get yourself into it in the first place. Break up your data
as small as you can BEFORE you enter it, and you won't have to do these
things to begin with.

[jlicciardi]

The third data segment doesn't always start at the 13th position,
sometimes there are more than 3 segments. The data is system generated.
There is no way to eliminate the irregular data.
thanks
Your function LastofChunk works fine. Thanks
pi********@hotm ail.com wrote:

jliccia...@mssy stems.com wrote:

I have a table with a field that has ( a sample )
13767;38355;520 270-1;
44795;38355;110 818;
13981;38355;563 550;

as data. I need to extract the last set of numbers in each row.
I have tried right$([name],len([name])- instr(1,[name],";")-1)
but to no avail

Please help
Hope this doesn't have to be fast... I could do it with strReverse

and then snip off the first semi-colon and then take everything between
position 1 and the next semi-colon, but that was way too much of a
headache. Will this work for ya?

Option Compare Database

Public Function LastChunkOfStri ng(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563 550;
'returns: 563550
' 13767;38355;520 270-1;
'returns: 520270-1
' 44795;38355;110 818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfStri ng = ChunkString(UBo und(ChunkString ) - 1)

End Function
so I _guess_ you could do it in SQL...

SELECT... ChunkString(UBo und(Split(strTe xt, strDelim, , vbTextCompare)) - 1)
FROM...
WHERE...

but I didn't mess with that... that's the fun you get to have!

BTW, I agree with Darryl - the best way out of this mess in the future is to not get yourself into it in the first place. Break up your data as small as you can BEFORE you enter it, and you won't have to do these things to begin with.