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

1. ## 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.

2. ## Re: Need to find text in a named range, and return part of the text to a single cell

So, return everything after the search criteria?

3. ## Re: Need to find text in a named range, and return part of the text to a single cell

Sorry, a set number of characters.

4. ## Re: Need to find text in a named range, and return part of the text to a single cell

Not sure if this is what you are looking for or not. Assuming your data is in column A, and the search string (RIM*RS) is in C1, use this...

=RIGHT(VLOOKUP(C1&"*",\$A\$1:\$A\$3,1,FALSE),LEN(VLOOKUP(C1&"*",\$A\$1:\$A\$3,1,FALSE))-LEN(C1))

Adjust ranges as needed. if you copy this down, and have other values in C2, C3 etc, it will pull out the other values too

5. ## Re: Need to find text in a named range, and return part of the text to a single cell

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.

6. ## Re: Need to find text in a named range, and return part of the text to a single cell

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.

7. ## Re: Need to find text in a named range, and return part of the text to a single cell

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 "".

8. ## Re: Need to find text in a named range, and return part of the text to a single cell

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1