find and list cells based on text on its next cell

Hi guys,

Here is my data:

steel 1998
wood 1998
alu 2000 zinc 1998
copper 1998
etc

I'd like to list data which labeled as 1998 so that I will have zinc 1998 on the list of the data instead of alu 2000

thank you for your help.

Hello Calif
Are you looking to remove data from your list that does not meet your condition : "something 1998" OR want to copy data to a results sheet that only contains "something 1998"
Also, does the data always follow the criteria |something|year| or does it vary ?

Regards

Hamjam

thanks Hamjam. the second option is what I need. I want to copy data that only contains xxx 1998 to the result sheet (hopefully the same sheet by clearing the rest of the data).
the data is vary. for instance xxx 1998, xxx EOY, etc. so it could be number and string.

Hello Calif
A possible solution is to add a column next to the data that contains the formula :

Please Login or Register  to view this content.

This formula is copied down to all the rows that contain data in the 1st column. I have used a value of 0 for the rows with no match to 1998
This means that Excel will take care of the error handling and show you position of the '1998'

Then that simplifies the code to look like this :

Please Login or Register  to view this content.

So the code just examines the start position and works backward to find the next space

Regards

Thanks Hamjam, but I could not get the data on the previous column such as steel, wood, etc.

Hello
You would have to attach a sample spreadsheet, your original post does not show that your data is in multiple columns.

Regards

Hamjam,

sorry for the lack of description. each of the data is in different column, so steel will be in column x and 1998 in column y and so on.

Hello
This changes the required code, as I thought all your data was in 1 column and you needed to look at characters inside that element to extract.

You can add a column after your data and insert a function :

Please Login or Register  to view this content.

I am not sure how many columns you have so I took it up to Z

This will provide the column number of the matched value

Then you can run a script (macro) that looks at all the values that are not #N/A and takes the value of the previous column (using offset). Then it can delete the content of the row and add your value + 1998

Do you want to give it a try ?

Regards

Thanks man. I am working on it now.