Hello,
I wondered if you could help. I'd like to search for @anyword-anylength, in column A all the way down until there is no more information, and if @anyword-anylength is found, enter @anyword-anylength in column D.
@anyword-anylength simply means a word that has @ in the first position and is any length. So an example of the contents of column A could be:
@one
@two
@three
@apple
@peaches
Thank you
Try this array formula in D2 and fill down; to array enter commit formula with Ctrl + Shift + Enter:A sample file is attached.Formula:
Please Login or Register to view this content.
Thank you. I see that it works, I just don't know how to get it started. How do I do that? Appreciated!
Last edited by punkin44; 03-06-2015 at 12:19 AM.
I don't understand the question. Could you clarify?
I used your sample. I entered the following in col A after your entries.
@one
@two
@three
@apple
@peaches
I then filled col D with the formula in D2 all the way down to meet what was at the end of col A, Then I tried pressing CNTRL SHIFT ENTER but nothing happened. Not sure how to get results.
ok, I got it to work, but it doesn't work past A13, even though I filled d2 down to d27, what should I do, please?
Sorry about my late reply.....had to step away for a day or so.Originally Posted by punkin44
The reason it won't work further down is because the range the formula references stops at its last row. For the additions listed I made this adjustment to the formula. It will work up to row 19.....your 18th item.If you anticipate more rows adjust accordingly. If it's going to vary change the $A$19 to something larger than anticipated. Make it sufficiently large enough to accommodate the longest anticipated list. For example if 100 items seems like a reasonable maximum set the range to $A2:$A$150.Formula:Please Login or Register to view this content.
Does this help?
Last edited by FlameRetired; 03-08-2015 at 01:16 PM.
I made the changes, changing 19 to 50 in D2, D3 and D4, but nothing happens after D4
Why not just usein cell D1 (and run it down), or do you want a consecutive list without empty cells in the column?Formula:Please Login or Register to view this content.
Good point.Why not just usein cell D1 (and run it down), or do you want a consecutive list without empty cells in the column?Formula:Please Login or Register to view this content.
Have you array entered? In D2 press the F2 key. Then while holding down Ctrl and Shift hit Enter. Then fill down. It worked on your sheet.
........and BTW have you seen JJGF's formula? It is much simpler and does not need to be array entered. If your results don't have to be grouped try that instead of the formula I posted.
Last edited by FlameRetired; 03-08-2015 at 04:02 PM.
Hey guys, thanks so much. I am using JJGF's because it does what I need, but thank you FlameR for your effort and support.
Super! Glad you found what works.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks