How to find the first Word in a range ?
 

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

How to find the first Word in a range ?
 

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
---------------
Jacob Skaria


"BaggieDan" wrote:

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

How to find the first Word in a range ?
 

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.

--
Biff
Microsoft Excel MVP


"BaggieDan" wrote in message
...
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

How to find the first Word in a range ?
 

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

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"BaggieDan" wrote:

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