I have created a function to search for specific characters. in a text string:
Public Function SearchCell(TargetCell As Range, SearchString As String)
SearchCell = InStr(1, LCase(TargetCell), LCase(SearchString))
I have used this function in a nested "IF" formula. However, the formula has ended up quite cumbersome. There are 5 nested "IFs" for 5 possible scenarios.
The attached workbook contains a table of the different expected scenarios, with the desired outcome and the formula used to achieve this.
1. Is there a less cumbersome way of doing this? I have used a formula, rather than a VBA loop, because I have found it to be faster, in previous experience.
2. Does excel have an inbuilt way of finding different text
3. I tried using the Excel "Find & Replace" functions, but could not manipulate them to deal with so many different scenarios.
i hope that my explanation is clear.
Thanks very much.