Need VBA Excel Solution For File Without a Defined Delimiter

[austlee]

Houston, We've Had A Problem.

Thank you in advance for anyone willing to solve this problem. I work for a large healthcare organization and in need of a solution for an excel file I receive from another department on a daily basis. Each cell within two columns labeled, "Primary Diagnosis" and "Secondary Diagnosis" of a 100 column worksheet contain multiple ICD-9 or ICD-10 diagnosis codes. Also, this daily file can contain any amount of rows/records so please factor this in. The ICD-9 and ICD-10 code set are the industry standard codes used throughout the entire healthcare system to describe a patients diagnosis for a medical encounter. For example, ICD-10 code "J18.9" is assigned to the description, "Community acquired pneumonia" and "C50.912" is "Malignant neoplasm of left breast".

Because each individual cell within the "Primary Diagnosis" field and "Secondary Diagnosis" fields contain many ICD-9 or ICD-10 codes, I am attempting break out the codes from these two columns into their respective column assignment of "First Diagnosis Description", "First Diagnosis Code", "Second Diagnosis Description", "Second Diagnosis Code", "Third Diagnosis Description", "Third Diagnosis Code", "Fourth Diagnosis Description", "Fourth Diagnosis Code" and so on. This expansion will go on until the 30th(60 columns) but not all 60 columns will be used depending on how many ICD-9/ICD-10 codes are housed within the cell.

I am very familiar with the text to columns function, however, it wont work to 100% accuracy because of the way each cell is configured in "Primary Diagnosis" field and "Secondary Diagnosis" columns. If one were to use the "Text to Columns" excel function with the delimiters set at ";" and "(" then the output to subsequent columns would place values in the incorrect columns. An example below, "Non-ST elevation myocardial infarction (NSTEMI), initial care episode (I21.4);" would output to subsequent columns as such, 1st Diagnosis Description -"Non-ST elevation myocardial infarction", 1st Diagnosis Code - "NSTEMI, initial care episode)" and 2nd Diagnosis Description - "I21.4)" instead of 2nd Diagnosis Description - "Non-ST elevation myocardial infarction (NSTEMI), initial care episode",1st Diagnosis Code -"I21.4)". Another example is, "Hypertension; Hypertension (I10);" will incorrectly output to, 1st Diagnosis Description - "Hypertension", 1st Diagnosis Code - "Hypertension", 2nd Diagnosis Description - "I10)" instead of 1st Diagnosis Description - "Hypertension Hypertension" and 1st Diagnosis Code- "I10)". If this seems confusing, try plugging in the below example into excel and use the text to columns function with delimiters being set at ";" and "(" to see an example of the output.

I will show a before and after example of what I am looking to achieve in the ideal sense-

Below is a small example of the file but without the 100 column layout I was referring to earlier:
Before
[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]Medical Record Number[/TD]
[TD]Provider[/TD]
[TD]Patient[/TD]
[TD]Primary Diagnosis[/TD]
[TD]Secondary Diagnosis[/TD]
[TD]Financial Class[/TD]
[TD]Report Date[/TD]
[TD]Billing Provider[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Doe, John S.[/TD]
[TD]Doe, Jane S.[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="width: 96"]Non-ST elevation myocardial infarction (NSTEMI), initial care episode (I21.4); Acute on chronic diastolic congestive heart failure, NYHA class 3 (I50.33); Multiple sclerosis (340);[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Acute respiratory failure (J96.00); AF (atrial fibrillation) (I48.91); Hypertension; Hypertension (I10); Pneumonia (J18.9);[/TD]
[TD]Medicare[/TD]
[TD]1/1/15[/TD]
[TD]Doe, John S.[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Doe, John S[/TD]
[TD]Doe, Jane S.[/TD]
[TD]Hypertension (401.9); Hypertension (I10);[/TD]
[TD]Acute respiratory failure (J96.00); AF (atrial fibrillation) (I48.91); Hypertension; Hypertension (I10); Pneumonia (J18.9);[/TD]
[TD]Oxford[/TD]
[TD]1/1/15[/TD]
[TD]Doe, John S.[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Doe, John S[/TD]
[TD]Doe, Jane S.[/TD]
[TD]Counseling regarding advanced directives and goals of care (Z71.89);[/TD]
[TD]Acute ischemic stroke (I63.50); Cardiac arrest (I46.9); Cerebral anoxic injury (G93.1); Cerebrovascular accident (CVA) (I63.9); Non-ST elevation myocardial infarction (NSTEMI), initial care episode (I21.4);[/TD]
[TD]Medicaid[/TD]
[TD]1/1/15[/TD]
[TD]Doe, John S.[/TD]
[/TR]
[TR]
[TD]AFTER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="class: grid, width: 700, align: left"]
<tbody>[TR]
[TD]Medical Record Number[/TD]
[TD]Provider[/TD]
[TD]Patient[/TD]
[TD]1st Diagnosis Description[/TD]
[TD]1st Diagnosis Code[/TD]
[TD]2nd Diagnosis Description[/TD]
[TD]2nd Diagnosis Code[/TD]
[TD]3rd Diagnosis Description[/TD]
[TD]3rd Diagnosis Code[/TD]
[TD]4th Diagnosis Description[/TD]
[TD]4th Diagnosis Code[/TD]
[TD]5th Diagnosis Description[/TD]
[TD]5th Diagnosis Code[/TD]
[TD]6th Diagnosis Description[/TD]
[TD]6th Diagnosis Code[/TD]
[TD]7th Diagnosis Description[/TD]
[TD]7th Diagnosis Code[/TD]
[TD]Financial Class[/TD]
[TD]Report Date[/TD]
[TD]Billing Provider[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Doe, John S.[/TD]
[TD]Doe, Jane S.[/TD]
[TD][TABLE="width: 150"]
<tbody>[TR]
[TD="width: 96"]Non-ST elevation myocardial infarction (NSTEMI), initial care episode[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]I21.4[/TD]
[TD]Acute on chronic diastolic congestive heart failure, NYHA class 3[/TD]
[TD]I50.33[/TD]
[TD]Multiple sclerosis[/TD]
[TD]340[/TD]
[TD]Acute respiratory failure[/TD]
[TD]J96.00[/TD]
[TD]AF (atrial fibrillation)[/TD]
[TD]I48.91[/TD]
[TD]Hypertension; Hypertension[/TD]
[TD]I10[/TD]
[TD]Pneumonia[/TD]
[TD]J18.9[/TD]
[TD]Medicare[/TD]
[TD]1/1/15[/TD]
[TD]Doe, John S.[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Doe, John S[/TD]
[TD]Doe, Jane S.[/TD]
[TD]Hypertension[/TD]
[TD]401.9[/TD]
[TD]Hypertension[/TD]
[TD]I10[/TD]
[TD]Acute respiratory failure[/TD]
[TD]J96.00[/TD]
[TD]AF (atrial fibrillation)[/TD]
[TD]I48.91[/TD]
[TD]Hypertension; Hypertension[/TD]
[TD]I10[/TD]
[TD]Pneumonia[/TD]
[TD]J18.9[/TD]
[TD][/TD]
[TD][/TD]
[TD]Oxford[/TD]
[TD]1/1/15[/TD]
[TD]Doe, John S.[/TD]
[/TR]
[TR]
[TD]123456[/TD]
[TD]Doe, John S[/TD]
[TD]Doe, Jane S.[/TD]
[TD]Counseling regarding advanced directives and goals of care (Z71.89);[/TD]
[TD]Z71.89[/TD]
[TD]Acute ischemic stroke[/TD]
[TD]I63.50[/TD]
[TD]Cardiac arrest[/TD]
[TD]I46.9[/TD]
[TD]Cerebral anoxic injury[/TD]
[TD]G93.1[/TD]
[TD]Cerebrovascular accident (CVA)[/TD]
[TD]I63.9[/TD]
[TD]Non-ST elevation myocardial infarction (NSTEMI), initial care episode[/TD]
[TD]I21.4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Medicaid[/TD]
[TD]1/1/15[/TD]
[TD]Doe, John S.[/TD]
[/TR]
</tbody>[/TABLE]

 

[Norie]

So the initial data only has 8 columns?

 

[austlee]

Thanks for responding....The original excel file contains 33 columns with anywhere between 1 through N rows. 2 out of these 33 columns are assigned to "Primary Dx" and "Secondary Dx" and this should be expanded out to 93 columns because of 60 new diagnosis columns/attributes ( e.g., 1st Diagnosis Description, 1st Diagnosis Code, 2nd Diagnosis Description, 2nd Diagnosis Code, 3rd Diagnosis Description, 3rd Diagnosis Code etc.....

 

[austlee]

Also, please keep in mind that the diagnosis code contained within parenthesis(e.g.,(I21.4)) will always contain either all numbers or a mix of strings and numbers. If any value within a parenthesis contains ONLY letters then that belongs in the diagnosis description column. The "Non-ST elevation myocardial infarction (NSTEMI), initial care episode (I21.4);" is a perfect example of this. In addition, another issue is when the cell contain a value like: "Hypertension; Hypertension (I10);" in which "Hypertension Hypertension" belongs in the Diagnosis Description column and "I10" belongs in the Diagnosis Code column.

 

[austlee]

Hi Norie, Any advice on this post?

Thanks