How to find the first Word in a range ?

[BaggieDan]

Hello,

I have lots of Rows that contain place names, for example A5:AA5, A19:AA19
and what I want to do is find the first place (left hand most name) in each
row.

There will be a number of blanks in some rows before you get to the first
name, so although the range is A5:AA5 the first place name may not appear
until C5 where as in the range A19:AA19 the first name may be in cell G5.

I have a simple macro that removes some places according to a selection the
user can make. So the lists will be different depending on the selection.

In a nutshell - I have a list in a Row and I want to find the left hand most
word.

Hope this is understandable!

Cheers

 

[Jacob Skaria]

Try the below formula. Please note that this is an array formula. Within the
cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply
this formula. If successful in 'Formula Bar' you can notice the curly braces
at both ends like "{=<formula>}"

=INDEX(A5:AA5,,MIN(IF(A5:AA5<>"",COLUMN(A5:AA5))))

If this post helps click Yes

 

[T. Valko]

Assuming empty cells are empty and don't contain formula blanks:

=INDEX(A5:AA5,MATCH("*",A5:AA5,0))

That will return the leftmost *text* value.

 

[BaggieDan]

Thanks to both for replying, yes the range does contain formula blanks
(should have included that in the initial question), but Jacobs reply solved
the problem.

Thanks again,

Dan