CODEWITHSUNDEEP
arraysexcelsubstring
sayth
sayth

Reputation: 7048

Excel - Find a word in a list of cells and return from first number

The issue I have is that I will be pasting from an external program and the order of the words can vary as between 1-7 possible entries.

What am wanting to do is format and copy by identifying the word in a list of cells and then splitting on condition.

So form example (note text and number are in same cell)

        #VALUE!
Time   6:30 
Year   4:30 
Date All   23   
Variance   14:30

I can find the number occurrence with

=FIND({0,1,2,3,4,5,6,7,8,9},L4,1)

However while I can find the first occurrence in this case Time using an array

{=FIND("Time",$L$16:$L$19,1)}

However all the subsequent fail

{=FIND("Year",$L$16:$L$19,1)}

How can I access them?

Upvotes: 0

Views: 65

Answers (1)

Gabor
Gabor

Reputation: 695

There seems to be a problem in your array formula. You are getting an error because it is not iterating through the array.

Try this:

=IFERROR(SMALL(IF(LEFT($L$16:$L$19,4)="Time",ROW($L$16:$L$19)-MIN(ROW($L$16:$L$19))+1),1),"")

What this essentially does is to iterate through your values and check if the first 4 letters correspond to what you are looking for. If it finds your word it will give you the row number of the row where the text was found. If it doesn't find anything, the cell will remain blank.

You could now use the formula 

=FIND("Time";OFFSET($L$16;L20-1;0))

to look in the given cell for the text you would like to find (assuming that the result is in cell L20).

Upvotes: 1

Related Questions