How to find the first Word in a range ?

  • Thread starter Thread starter BaggieDan
  • Start date Start date
B

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
 
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
 
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.
 
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
 
Back
Top