Parse one field's data into multiple fields

[jlicciardi]

I have a table with a field that has ( a sample )
13767;38355;520270-1;
44795;38355;110818;
13981;38355;563550;

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********@mssystems.com> wrote:

I have a table with a field that has ( a sample )
13767;38355;520270-1;
44795;38355;110818;
13981;38355;563550;

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********@mssystems.com> wrote:

I have a table with a field that has ( a sample )
13767;38355;520270-1;
44795;38355;110818;
13981;38355;563550;

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]

[e-mail] wrote:

I have a table with a field that has ( a sample )
13767;38355;520270-1;
44795;38355;110818;
13981;38355;563550;

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 LastChunkOfString(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563550;
'returns: 563550
' 13767;38355;520270-1;
'returns: 520270-1
' 44795;38355;110818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfString = ChunkString(UBound(ChunkString) - 1)

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

SELECT... ChunkString(UBound(Split(strText, 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]

[e-mail] wrote:

I have a table with a field that has ( a sample )
13767;38355;520270-1;
44795;38355;110818;
13981;38355;563550;

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 LastChunkOfString(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563550;
'returns: 563550
' 13767;38355;520270-1;
'returns: 520270-1
' 44795;38355;110818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfString = ChunkString(UBound(ChunkString) - 1)

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

SELECT... ChunkString(UBound(Split(strText, 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********@hotmail.com wrote:

[e-mail] wrote:

I have a table with a field that has ( a sample )
13767;38355;520270-1;
44795;38355;110818;
13981;38355;563550;

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 LastChunkOfString(ByVal strText As String, ByVal
strDelim As String) As Variant
'Sample inputs and outputs... note, these are TEXT.
' 13981;38355;563550;
'returns: 563550
' 13767;38355;520270-1;
'returns: 520270-1
' 44795;38355;110818;
'returns: 110818

Dim ChunkString As Variant
ChunkString = Split(strText, strDelim, , vbTextCompare)
LastChunkOfString = ChunkString(UBound(ChunkString) - 1)

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

SELECT... ChunkString(UBound(Split(strText, 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.