Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
Find the MAX number in range, then find... | Excel Discussion (Misc queries) | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
Find 2nd instance of a word in a range. | Excel Worksheet Functions | |||
Find dates in a range; then sum values in that range by a criteria | Excel Discussion (Misc queries) |