Hi
I am using the following formula to check whether a text string contains a word that is on a list.
This returns the start position if a word in the list is found in the text string. What I would like it to return is the value it matched on. So if in cell C1 I had 'random text Data random text' in stead of returning the number 13, the starting position of the word 'Data' I would like it to return the word 'Data'.
I could use a simple Mid() formula, but the number of characters will change depending on the word found. i.e. Data is only 4 characters, where Telecoms is 7.
Also I am ideally not wanting it to look for the next space, as the word may have a number bolted on to it. i.e. Data2.
Sample Spreadsheet.
Thanks in advance!
I am using the following formula to check whether a text string contains a word that is on a list.
Code:
=MIN(SEARCH({"Power","Data","Telecoms"},C1&"PowerDataTelecoms"))This returns the start position if a word in the list is found in the text string. What I would like it to return is the value it matched on. So if in cell C1 I had 'random text Data random text' in stead of returning the number 13, the starting position of the word 'Data' I would like it to return the word 'Data'.
I could use a simple Mid() formula, but the number of characters will change depending on the word found. i.e. Data is only 4 characters, where Telecoms is 7.
Also I am ideally not wanting it to look for the next space, as the word may have a number bolted on to it. i.e. Data2.
Sample Spreadsheet.
Thanks in advance!
