Need to find text in a named range, and return part of the text to a single cell

Hi Gurus,

I am not sure if this is completed by a formula or VBA, but I think a formula would work.

Column A
RIM*RT 123456
RIM*RS 654321
RIM*RW TestName

The above example is in the named range (Text), the whole values sit in column A. I need a formula that will look at the named range and if it finds RIM*RS return 654321. I have a lot of cells in Column A, so just a bunch of ifs, will not work. I would like one formula I could put in cell D1, that would return the 654321,if the criteria is met. Then have it look for RIM*RT in say cell E1. Please let me know if you need a better explanation.

Thanks in advance.

Sorry, a set number of characters.

Try this in D1 and copy all the way down.

Please Login or Register  to view this content.

If the size of the information to be returned is longer that 20 characters, you will need to change that to a larger number.

Thank you very much. I am much closer to what I am trying to do, thanks to your help. If I wanted to limit the return to a set number of characters, could that be done? Say only the first 4 from the result of you provided.

Thanks again.

Yes, change the 20 to a 4 or however many digits you want to return. If you want a blank instead of the 0 change the last 0 to "".

Here is the breakdown on the formula

Please Login or Register  to view this content.

SEARCH("RIM*RS",A1) is looking in cell A1 for RIM*RS
IFERROR (search,0) will return a 0 if the search function errors out.
MID(A1,8,20) grabs the information in cell A1 starting at the 8th character and continues for 20 characters.
Clean (mid) removes the non printable characters, i.e. any blank spaces at the end.

IF (error check = 1, cleanmid, 0) will return the requested information if RIM*RS search is valid, or a 0 if not.