459,685 Members | 1,681 Online
Need help? Post your question and get tips & solutions from a community of 459,685 IT Pros & Developers. It's quick & easy.

# Parse one field's data into multiple fields

 P: n/a 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 Nov 13 '05 #1
5 Replies

 P: n/a On 21 Feb 2005 12:51:05 -0800, 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 Nov 13 '05 #2

 P: n/a On 21 Feb 2005 12:51:05 -0800, 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 Nov 13 '05 #3

 P: n/a jliccia...@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 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. Nov 13 '05 #4

 P: n/a jliccia...@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 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. Nov 13 '05 #5

 P: n/a 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: jliccia...@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 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. Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.