Help!:Extract Text Using Mid Function

[Guest]

Help!
I need to pull this data from a field for a report.

Field Name:O_Text

The are 323 records and they vary in the following ways:

1. Debris, Slight, from 05 to 07 o´clock, GENERAL COMMENTS: Dirt.
2. "TEE" Connection at 03 o´clock
3. Corrosion, Large, from 05 to 07 and 11 to 1 o´clock, GENERAL COMMENTS:
Continuing.
4. Corrosion, Moderate, from 05 to 07and at 12 o´clock , GENERAL COMMENTS:
Continuing.

This is how they want the field to read.

New Field Name: O_Position

1. 05-07
2. 03
3. 05-07, 11-01
4. 05-07, 12


They want all the text gone and just the numbers with a "-", or a ","
seperating the values wherever needed.

Thank you!!!!!!

Maggie Minnamon
IT Manger

 

[John Vinson]

Help!
I need to pull this data from a field for a report.

Field Name:O_Text

The are 323 records and they vary in the following ways:

1. Debris, Slight, from 05 to 07 o´clock, GENERAL COMMENTS: Dirt.
2. "TEE" Connection at 03 o´clock
3. Corrosion, Large, from 05 to 07 and 11 to 1 o´clock, GENERAL COMMENTS:
Continuing.
4. Corrosion, Moderate, from 05 to 07and at 12 o´clock , GENERAL COMMENTS:
Continuing.

This is how they want the field to read.

New Field Name: O_Position

1. 05-07
2. 03
3. 05-07, 11-01
4. 05-07, 12


They want all the text gone and just the numbers with a "-", or a ","
seperating the values wherever needed.

That's going to be VERY difficult, especially if hyphens or commas
occur elsewhere in the field. Worse, you're assuming that a dumb
computer can recognize that the word "to" is equivalent to the
character "-".

YOu'll need some pretty sophisticated VBA code to parse this (human
meaningful, but natural language perception by computers is still a
very advanced topic) text and determine which character strings "to"
need to be replaced and which don't (e.g. "GENERAL COMMENTS: This
tends to happen on rainy days" or even "GENERAL COMMENTS: This only
happens from 10 am to 3 pm").

Is there ANY chance that this complex, multivalued, non-atomic text
field can be eliminated and replaced by a normalized table structure,
with fields [Problem] (e.g. Debris, Corrosion, TEE connection),
]Severity], [GeneralComments], and a one-to-many related table of
Location? It's MUCH easier to put separate atomic elements together
than it is to parse English language sentences!

John W. Vinson[MVP]

 

[TT]

Help!
I need to pull this data from a field for a report.

Field Name:O_Text

The are 323 records and they vary in the following ways:

1. Debris, Slight, from 05 to 07 o´clock, GENERAL COMMENTS: Dirt.
2. "TEE" Connection at 03 o´clock
3. Corrosion, Large, from 05 to 07 and 11 to 1 o´clock, GENERAL COMMENTS:
Continuing.
4. Corrosion, Moderate, from 05 to 07and at 12 o´clock , GENERAL COMMENTS:
Continuing.

This is how they want the field to read.

New Field Name: O_Position

1. 05-07
2. 03
3. 05-07, 11-01
4. 05-07, 12


They want all the text gone and just the numbers with a "-", or a ","
seperating the values wherever needed.

That's going to be VERY difficult, especially if hyphens or commas
occur elsewhere in the field. Worse, you're assuming that a dumb
computer can recognize that the word "to" is equivalent to the
character "-".

YOu'll need some pretty sophisticated VBA code to parse this (human
meaningful, but natural language perception by computers is still a
very advanced topic) text and determine which character strings "to"
need to be replaced and which don't (e.g. "GENERAL COMMENTS: This
tends to happen on rainy days" or even "GENERAL COMMENTS: This only
happens from 10 am to 3 pm").

Is there ANY chance that this complex, multivalued, non-atomic text
field can be eliminated and replaced by a normalized table structure,
with fields [Problem] (e.g. Debris, Corrosion, TEE connection),
]Severity], [GeneralComments], and a one-to-many related table of
Location? It's MUCH easier to put separate atomic elements together
than it is to parse English language sentences!

John W. Vinson[MVP]

This may not be too bad provided that digits do not occur before the
substring that actually contains time values. Here is an approximate
algorithm:

1) Copy from the position of the first digit and stop when a comma or end of
string is encountered.

2) Trim the string up to the first digit.

3) If the resultsstring is not blank then append a "," to the resultstring.
Copy the first 1 or 2 characters from the sourcestring, taking only digits.
Append the 1 or 2 characters to the resultstring.

4) If the next character is a "to" or a "-", then append a "-" to the
results string and append the next 1 or 2 digits encountered provided that
there is only whitespace encountered after the "to" or "-".

5) Repeat 1-4 until no more digits are found.