Count first occurance of text

I have a row of 24 cells, some are blank and others have text.  I am trying 
to create a formula that returns the first instance of text being used.  I 
have tried using a match, but it gives me an error because I am trying to 
pull text not a number.

thanks
0
Utf
11/14/2009 11:46:05 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
2143 Views

Similar Articles

[PageSpeed] 22

Here's one dart throw ..  
Assume your 24 source cells are A2:A25
Place in say, B2, normal ENTER to confirm:
=INDEX(A2:A25,MATCH(TRUE,INDEX(ISTEXT(A2:A25),),0))
Bullseye? hit the YES below
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
--- 
"Chad Wodskow" wrote:
> I have a row of 24 cells, some are blank and others have text.  I am trying 
> to create a formula that returns the first instance of text being used.  I 
> have tried using a match, but it gives me an error because I am trying to 
> pull text not a number.
> 
> thanks
0
Utf
11/14/2009 11:56:01 PM
=INDEX(A1:Z1,MATCH(TRUE,A1:Z1<>"",0))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather 
than just the enter key.
-- 
Gary''s Student - gsnu200908


"Chad Wodskow" wrote:

> I have a row of 24 cells, some are blank and others have text.  I am trying 
> to create a formula that returns the first instance of text being used.  I 
> have tried using a match, but it gives me an error because I am trying to 
> pull text not a number.
> 
> thanks
0
Utf
11/14/2009 11:59:01 PM
The earlier presumes you want to extract the 1st occurence of TEXT in A2 down

If you just want to extract the contents of the 1st non empty cell, 
irrespective whether its a real number or text number or text string
use this in B2, normal ENTER to confirm:
=INDEX(A2:A25,MATCH(TRUE,INDEX(A2:A25<>"",),0))
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
0
Utf
11/15/2009 12:12:01 AM
Max,

That worked perfectly, now how do I return the 2nd instance?

Chad

"Chad Wodskow" wrote:

> I have a row of 24 cells, some are blank and others have text.  I am trying 
> to create a formula that returns the first instance of text being used.  I 
> have tried using a match, but it gives me an error because I am trying to 
> pull text not a number.
> 
> thanks
0
Utf
11/15/2009 1:53:01 AM
This will return the first TEXT entry in the range (excludes formula 
blanks):

=INDEX(A2:A25,MATCH("?*",A2:A25,0))

>how do I return the 2nd instance?

Try this...

Assume you want the results starting in cell C2.

Array entered** in C2 and copied down as needed:

=IF(ROWS(C$2:C2)>COUNTIF(A$2:A$25,"<>"),"",INDEX(A:A,SMALL(IF(A$2:A$25<>"",ROW(A$2:A$25)),ROWS(C$2:C2))))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

C2 will return the 1st non-empty cell
C3 will return the 2nd non-empty cell
C4 will return the 3rd non-empty cell
etc
etc

-- 
Biff
Microsoft Excel MVP


"Chad Wodskow" <ChadWodskow@discussions.microsoft.com> wrote in message 
news:B27209D4-EC11-4489-9847-A61B4D6BFB7A@microsoft.com...
> Max,
>
> That worked perfectly, now how do I return the 2nd instance?
>
> Chad
>
> "Chad Wodskow" wrote:
>
>> I have a row of 24 cells, some are blank and others have text.  I am 
>> trying
>> to create a formula that returns the first instance of text being used. 
>> I
>> have tried using a match, but it gives me an error because I am trying to
>> pull text not a number.
>>
>> thanks 


0
T
11/15/2009 3:43:09 AM
> .. how do I return the 2nd instance?
Put this in B2, array-enter, ie press CTRL+SHIFT+ENTER to confirm:
=INDEX(A:A,SMALL(IF(ISTEXT(A$2:A$25),ROW(A$2:A$25)),ROWS($1:1)))
Copy down as far as required. B2 returns the 1st TEXT data in A2 down, B3 
returns the 2nd TEXT data (2nd instance), and so on. #NUM! will signal the 
exhaustion of all TEXT data
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
0
Utf
11/15/2009 10:22:01 AM
Reply:

Similar Artilces:

Alignment for text vs numbers
If I put a literal text string ("This is a literal text string") in a cell that is too wide for that cell, the text will spill over into adjacent cells, if they are empty. However, if I put a number in that cell and then apply a custom format, like ("Results for "mm/dd/yy), it fills the cell with #'s if it's too wide. Is there a way to get the formatted number to behave like text? (Without merging cells.) If you select the cell and any empties to the right and format Center Across Selection. The full text will be displayed. Sadly, it will appear centered, but ...

Excel
I have exported TFS work items into Excel. This includes text that are very long, and spanning over multiple lines. Whenever I click this cell. The contents revert to hashes, making it very difficult to work with (or print) work items in Excel. I have tried formating the cell to custom format, but clicking in the cell losing this formating. Is there any way to remove the hashes? Ensure that the cell is formatted as General rather than Text, and once you have changed the format then click in that cell again, press F2 and then <enter> to ensure that the format change takes effect...

Excel = MS Word, Text Functions?, Edit | Fill | Justify??
Hello, I posted a question at excel.misc regarding using excel as a word processor. http://groups-beta.google.com/group/microsoft.public.excel.misc/browse_frm/thread/790757e9a328eee3/9002ea974dde7cc1 I was attempting to use text functions (trim, mid, etc. ) to "wrap" text as it would in MS Word. And Myrna brought to my attention that I could use edit | fill | justify option to somewhat accomplish what I'm trying to do. Is there another alternative using macros? There's almost always an alternative using macros. Whether it's an alternative that makes sense to do dep...

Text Boxes and Font
Is there a way to high light all the texts in the text boxes at once (incase you need to change the size of fonts etc) i have tried with control but it highlights the actual text box. i was wondering if it was possible to do this without using the format painter becauser tghis is time consuming when you have abvout 70 text boxes to change the font or size in. thanks for any feedback Patch <Patch@discussions.microsoft.com> was very recently heard to utter: > Is there a way to high light all the texts in the text boxes at once > (incase you need to change the size of fonts et...

Text Placement on Bars in Gaant Chart
I would like to add text to the top of summary bars, as several strech for several months and it would be more convenient for the viewer to have the title centered rather than offset to the side (right). When I alter the format of the bar text for the summary bars, however, it increases the distance between all of the bars within my entire project. The additional space added to my project makes viewing the project extremely difficult. For the dozen or so shorter tasks within the summary, having the text off to the right is fine. Is there a way that I can have text placement a...

Code Snippet of Display Text on Button Control
Hi, I want to display a text on a button which is dragged from the toolbox and placed on the dialog. I have posted a message regarding this ----> "Displaying text on a controls" I got the reply. But it is not still working Here is the code snippet..... MyApplicationDlg.h CButton m_button_control; MyApplicationDlg.cpp void CMyApplicationDlg::DoDataExchange(CDataExchange* pDX) { CDialog::DoDataExchange(pDX); DDX_Control(pDX, IDC_BUTTON_TEST, m_button_control); } BOOL CMyApplicationDlg::OnInitDialog() { CDialog::OnInitDialog(); (GetDlgItem(IDC_BUTTON1))->SetW...

open 2 forms with matching text box
I have a form with command button and text box how can I open another form but with condition textbox is equal in 2 forms I mean (matching) by command button on the main form. Hi, here is an example DoCmd.OpenForm "frm2ndForm", , , "([VisSiteNo] = " & Me.txtVisSiteNo & ") " --frm2ndForm is the name of the form to open --[VisSiteNo] is a number field, if [VisSiteNo] was a text field use "([VisSiteNo] = """ & Me.txtVisSiteNo & """) " --txtVisSiteNo is the name of the textbox on the 1st form, [V...

Text manipulation in update query
I want to compare two tables, in order to update fields in either table. The fields that I am matching on are text. Table 1 field key_num xxx-1234567-8_nn Table 2 field key_num xxx12345678 How can I parse out the hyphens and the _nn portion, so I can match on the fields? Mid([table1],1,13) Start at the 1st character and show the next 13. -- Milton Purdy ACCESS State of Arkansas "Tony in Michigan" wrote: > I want to compare two tables, in order to update fields in either table. The > fields that I am matching on are text. > Table 1 field key_...

MISSING TEXT 01-21-10
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Since i upgraded to snow leopard all of the text is missing from my emails. If i check my sent mail folder the email has the text. I have looked at all my settings and i can't find what i'm doing wrong. I have checked the colour just to make sure i'm not typing in white. any clues to what may be happening. many thanks <br> Guesty44 ...

Adding Item to Stock Count Schedule
Can an inventory item be added to a stock count schedule after the count has been started and counted quantities have been entered? Thanks! Hi Don, Once a Stock-count schedule is started, you can't add any other items... And you'll be able to cancel it only if there are not Qty entries made in the Stock-count Entry window.... or you cleared all data in the SC entry form (which is either by posting the SC or clearing all entered data Qty): However, if you are confortable enough with SQL, you may enter additionnal records in the table directly. Those tables are only used as work-...

copy text with comma eliminated
Dear all, I have a series of text seperate by comma in Column A A shop, table, toy, car mon, light, book radio, phone now I want to copy them one by one to Column B, with the comma eliminated B shop table toy car mon light book radio phone How to do it by formula? thanks Kent ...

Text Box color problem
Have text boxes filled with one colour gradient fill from the standard palette, ,90% transparency at the top (very light color) to 100% transparency at the bottom. all looks fine on screen yet when printed its ignoring the transparency and printing full colour at the top to 100% transparency at the bottom any ideas as to why this might be Office 2007, document saved in 2003 .doc format thanks -- bazwillrun ...

Text distortion issues in PowerPoint
I am having some text distortion issues in PowerPoint 2003. I think it may be a conversion issue - i.e., a 2007 template was applied to report created in 2003 and now some of the text is distorted. And interestingly, it's only in slideshow view, and it's not consistent throughout; sometimes the text is distorted and other times the numbers are distorted. It prints fine and looks fine in normal view. Are there any fixes for this? ...

Counting Individuals NOT Occurrences with than one criteria
I have a Excel 2007 Spreadsheet with 26 columns and 190 rows arranged as follows: A B C D E F 1 Harm Red Fully Ach Abstinence Partly Ach Harm Red Fully Ach 2 Harm Red Fully Ach Harm Red Fully Ach 3 Harm Red Partly Ach 4 Abstinence Not Ach Harm Red Partly Ach 5 Abstinence Partly Ach 6 Harm Red Fully Ach Harm Red Partly Ach 7 Harm Red Fully Ach Now I've been trying to use SUMPRODUCT and it only gives occurrences of say 'Harm Red' and 'Ful...

Item counts on X Report
Currently we see Department sales and % of total sales. Is there a way to replace the "Report.Department.PercentageSales" variable with a total item count for sales in that department? ...

What's the best free pdf to text or rtf converter?
Can't convert with free version of Foxit Reader, works well to read the new format pdf's, but does not convert to text free-pdf-to-word-converter.exe 588KB Free PDF to RTF Converter 1.5 seems to reorganize the simplest of pdf's For example a numbered list of text ends up with all the numbers AFTER each entry! I think I tried pdf2Wordsetup.exe 1,103KB and it was worse So, what free pdf to text, or to doc or to rtf works the best on Win98FE? Robert Robert A. Macy wrote: > Can't convert with free version of Foxit Reader, works well to read > the...

Count a value excluding duplicates depending on a set criteria
Hi, hope someone can help, I have data that has multiple entrys for a date, but I only want to count each day as one using a formula in excel 2007. A B C D 1 Dept Crew Type Date 2 301 A 1 1/02/2009 3 302 C 2 1/02/2009 4 301 A 1 1/02/2009 5 301 A 1 2/02/2009 6 302 C 2 1/02/2009 7 303 D 2 1/02/2009 8 301 B 1 9/02/2009 9 301 A 2 1/02/2009 10 303 D 2 9/02/2009 E.g. I want to know how many days a crew worked in the above, 'Dept' = "301", 'Crew' = "A", 'Type' = "1", how many days. Would equal 2 days. One way... ...

text effects
Are there text effects available somewhere in Excel similar to what is available in Word? I would like to have the text in a text box blink for emphasis. Hi Vicki, Thank goodness Excel does not have such "fonts". Usually if you see something blink in Excel, somebody used bad coding. However, if you promise that no one else but you would ever see such a spreadsheet Bill Manville's solution is ... Blinking cells, flashing cells by alternating interior color using ontime, http://j-walk.com/ss/excel/eee/eee002.txt Otherwise, ain't seen nothing, don't know not...

Text Shapes
How can I create text in various shapes (circle)? WordArt? -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Gardener" <Gardener@discussions.microsoft.com> wrote in message news:5523D7C5-6689-45C0-BC57-9360DE416336@microsoft.com... > How can I create text in various shapes (circle)? ...

Underline a portion of Text via VBA
In my report I have a textbox named: Text110 Controlsource ="Svc " & [Svc/Qtn] & " No.: " & [QtnNo] & [QtnAB] The result in the preview is like below : Svc Revised-Estimation No.2010/2356 My requirement is to under line only the portion 2010/2356 only via VBA code. I may have put the line manually under the text, but the description inside the text keep changing some time Estimate, some time Revised-Estimate, therefore the allingment of the line underneath changes. That is the reason for a code, which will draw a line exactly calculatin...

Why are extra newlines added to Plain Text messages I receive?
Every time a message arrives in my inbox (Outlook 2003), it contains 2 line breaks for every line ending. What do I do to correct this? Thanks, Mike ...

How to select text, e.g. simulate the text copied with ctrl-a on a webbrowser control in C#? I tried this and it always returns null for range.Text private void Form1_Load(object sende
How to select text, e.g. simulate the text copied with ctrl-a on a webbrowser control in C#? I tried this and it always returns null for range.Text private void Form1_Load(object sender, EventArgs e) { webBrowser1.Navigate("http://www.google.com/"); } private void button1_Click(object sender, EventArgs e) { IHTMLDocument2 htmlDocument = webBrowser1.Document.DomDocument as IHTMLDocument2; IHTMLSelectionObject currentSelection = htmlDocument.selection; if (currentSelection != nul...

Select all dates in month from month and year in text box?
I have a form to run reports. All reports are run based on the month and year but the table data contains actual dates. On the form the client enters 03/2007 in a text box to select reports containing data for March 2007. The text box has a format mm/yyyy. The table data has fields defined as Date/Time. Data looks like 3/15/2007. How can I use the month and year from the text box my form to select all of the dates that fall within the month and year requested? -- Message posted via http://www.accessmonster.com In the criteria of the query that drives the report: Between [Forms]![frm...

RE: Counting groups exact case numbers
The only problem I have with this formula is that it only works when there are case numbers exactly in the range given. I need to use the range of an entire column, top to bottom, so that as case numbers (numerical or alpha-numerical) get added to the list the formula will count the different case numbers (ongoing) and not count the duplicate ones. If this makes sense. Dude, you're awesome, or are you female? anyway, can this be done? "tjtjjtjt" wrote: > Try: > =SUM(IF(FREQUENCY(MATCH(B1:B10,B1:B10,0),MATCH(B1:B10,B1:B10,0))>0,1)) > > tj > > &quo...

How to make static text control hyperlink with tool tip ???
Hi Could anybody let me know how to create (Dynamically) a static text control with hyperlink features + multiline tool tip. Any code snippet / source or hints will be highly appreciated. Thanks in advance. Mujtaba http://www.codeproject.com/staticctrl/cmyhyperlink.asp Tom <tomujtaba@hotmail.com> wrote in message news:1119011497.785937.25900@g43g2000cwa.googlegroups.com... > Hi > > Could anybody let me know how to create (Dynamically) a static text > control with hyperlink features + multiline tool tip. > > Any code snippet / source or hints will be highly appr...