Searching certain part of text in a cell using Index

**thelegazy** · 06-25-2013, 12:54 AM

Hi all,

I was wondering if it is possible to search a certain part of a cell using the index function. Just as a basic example, I have a part number such as X123, but it may have variations made to it that become X123A1, X123A2 and so on.

At the moment I have an excel sheet that is using an Index Formula to find all the necessary parts to help build a part, but it is based solely on a part number. I want to be able to pull all the variations for the part and have them grouped by their part number. Can anyone help me with this?

**abousetta** · 06-25-2013, 12:56 AM

Instead of X123 use:

Formula:

Please Login or Register  to view this content.

this will bring up cells that contain X123.

abousetta

**thelegazy** · 06-25-2013, 07:13 PM

Sorry,

I should have posted an example workbook with my fist post. Please find one attached in this post. If you look at the "Database" worksheet you can see that there are two parts that start with X123, I want to be able to pull all the information related to them as displayed on the first page for "X123A2". I want to be able to put in X123A1 and have excel show me all the parts that start with X123 and the tools for each one. Is this possible?

**benishiryo** · 06-25-2013, 11:11 PM

hi there. not sure if i understood correctly. if you want to key in "X123A2" in B3 & only take in the first 4 characters to extract the list:

Formula:

Please Login or Register  to view this content.

if you want to key in "X123A2" in B3 & extract everything that begins with "X123A2", then:

Formula:

Please Login or Register  to view this content.

this way, if you type in "X123", it will give you both "X123A1" & "X123A2".

**thelegazy** · 07-01-2013, 02:24 PM

Thank you so much, the first one worked.

I just had one more question now. I used conditional formatting to hide the duplicate values for the two different parts, but now is there a way for me to get the formulas in columns b-g to pull the information for the two different parts?