Forum Discussion

Marit585's avatar
Marit585
Copper Contributor
Nov 07, 2021

Excel: extract word from cell and lookup in another sheet to match

Greetings,

 

My task is

1) to extract from sheet "financing codes" an ICD-10 code (diagnose classification) from column B, which provides information/ definition about the financing code that is provided in column A.

2) Next, I have to look up the extracted ICD-10 code in the sheet ACSC (ambulatory care sensitive condition), and based on column A that also contains ICD 10 codes using vlookup bring the name of the icd code provided in column b.

 

In an attached dataset from sheet "Financing codes" where I have two columns - financing code (column A) and the definition of this code (column b) I want to extract data from column b where I have many words in one cell that is separated using "-/-". (this column contains words written in Georgian (this is not of my interest) and ICD-10 - the diagnosed code that is a combination of one letter and numbers, sometimes uses "." as is no more than 5 digit - is the one which I want to find and extract from cell). 

 

Challenge- these ICD code doesn't always have the same order in the cell, so I can't just use LFT, MID, or the right formula. For example, B8 contains this code definition which is written in one cell

 -/- ჰიპოტენზია -/- I95.9 -/- ეკგ, არტერიული წნევის ნორმალიზება - here I am interested to extract the word in bold - I95.9. Note that not all cells contain this type of code. the only thing that this ICD -10 code has in common is the structure it is no more than 5 digits, uses one letter and numbers. it usually has this type of format J95 or J95.9.

 

 

 

 

 

 

Resources