Forming a binary column from a more complicated column of diagnostic codes

[MMD]

Hello

I am in need of a quick answer to a problem I am dealing with and so I
would be most grateful if you can provide one.

I have a long column of data containing diagnostic codes. A typical
entry containing 6 codes looks like this: J81.0 K37.4 I89.X J15.2
J19.X J33.0. However, the number of codes in any one cell can range
from none at all (indicated by a blank cell) up to 6.
Also, although each code has a decimal point, the string to the left
and right of each such decimal point may contain letters or numbers of
which there are at most three on the LHS of the decimal point and at
most 2 on the RHS of the decimal point. The object of the exercise
is to form a new column containing a '1' whenever the letter 'I' has
been detected as the first letter of at least one diagnostic code
within a given cell, and a '0' whenever no diagnostic code within a
given cell commences with the letter 'I'.

Could you kindly provide an if-then statement which I can write as an
Excel logic function to derive the new binary column from the more
complex column.

Thank you very much in advance for your kind assistance.

Regards

Margaret

 

[Ron Rosenfeld]

Hello

I am in need of a quick answer to a problem I am dealing with and so I
would be most grateful if you can provide one.

I have a long column of data containing diagnostic codes. A typical
entry containing 6 codes looks like this: J81.0 K37.4 I89.X J15.2
J19.X J33.0. However, the number of codes in any one cell can range
from none at all (indicated by a blank cell) up to 6.
Also, although each code has a decimal point, the string to the left
and right of each such decimal point may contain letters or numbers of
which there are at most three on the LHS of the decimal point and at
most 2 on the RHS of the decimal point. The object of the exercise
is to form a new column containing a '1' whenever the letter 'I' has
been detected as the first letter of at least one diagnostic code
within a given cell, and a '0' whenever no diagnostic code within a
given cell commences with the letter 'I'.

Could you kindly provide an if-then statement which I can write as an
Excel logic function to derive the new binary column from the more
complex column.

Thank you very much in advance for your kind assistance.

Regards

Margaret

Here's one way:

Download and install Longre's free (and easily distributable) morefunc.xll
add-in from http://xcell05.free.fr

Then use this formula, with your string in A1:

=--REGEX.COMP(A1,"\bI")

The formula checks merely that one of the substrings BEGINS with the letter I.

If your checking needs to be more involved; in other words, meet the criteria
you lay out above for a legal code, then:

=--REGEX.COMP(A1,"\bI\w{0,2}\.\w{0,2}\b")

looks for a string that

Start with "I"
then 0 to 2 more characters (total of 3)
then "."
then 0 to 2 more characters
then the End.

--ron