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

Copper Contributor

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.

 

 

 

 

 

 

1 Reply

@Marit585 

Hello! You've posted your question in the Tech Community Discussion space, which is intended for discussion around the Tech Community website itself, not product questions. I'm moving your question to the Excel space - please post Excel questions here in the future.