Breaking up data

  • Thread starter Thread starter TeeSee
  • Start date Start date
T

TeeSee

I have, at this time, a simple table containing data in ONE field. The
next line is a sample of this data.
CLASS 00000340:SEALANT/CAULKING
I would like to split this data into two fields ..... the first one
containing CLASS up to and containing the colon and the second
containing the remainder. While I'm sure this has been addressed
before I can't seem to come up with the correct input for the search
criteria in the knowledgebase. Any help appreciated.
 
I have, at this time, a simple table containing data in ONE field. The
next line is a sample of this data.
CLASS 00000340:SEALANT/CAULKING
I would like to split this data into two fields ..... the first one
containing CLASS up to and containing the colon and the second
containing the remainder. While I'm sure this has been addressed
before I can't seem to come up with the correct input for the search
criteria in the knowledgebase. Any help appreciated.
Click to expand...

=Left([FieldName],InStr([FieldName],":"))
CLASS 00000340:

= Mid([FieldName],InStr([FieldName],":")+1)
SEALANT/CAULKING
 
Hello "TeeSee".

TeeSee said:
I have, at this time, a simple table containing data in ONE field.
The next line is a sample of this data.
CLASS 00000340:SEALANT/CAULKING
I would like to split this data into two fields ..... the first one
containing CLASS up to and containing the colon and the second
containing the remainder. While I'm sure this has been addressed
before I can't seem to come up with the correct input for the
search criteria in the knowledgebase. Any help appreciated.
Click to expand...

You can write two functions in a standard module that use the split
function:

Function Part1(TextWithColon)
Dim a() As String
Part1 = Null
If IsNull(TextWithColon) Then Exit Function
a = Split(TextWithColon, ":", 2)
If UBound(a) >= 0 Then Part1 = a(0) & ":"
End Function

Function Part2(TextWithColon)
Dim a() As String
Part2 = Null
If IsNull(TextWithColon) Then Exit Function
a = Split(TextWithColon, ":", 2)
If UBound(a) >= 1 Then Part2 = a(1)
End Function

These functions can be used in an update, append or create table
query to "split" the line into two parts.
 
Back
Top