How to count merged cells

Can someone help me in developing a formula that will:
1.  Count how many merged cells in a column that are created from 2 cells 
AND contain both text and numbers?
2.  Count how many merged cells in a column that are created from 3 cells 
AND contain both text and numbers?
3.  Council how may merged cells in a column that are blank.
3
Utf
4/8/2010 11:43:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
3838 Views

Similar Articles

[PageSpeed] 46

If you look in the archives of this group you will see countless examples of 
reasons not to use merged cells.
To concatenate cells is fine, to merge is asking for trouble.
--
David Biddulph


"Excel Ella" <ExcelElla@discussions.microsoft.com> wrote in message 
news:E60F0B1D-FB18-4864-95A2-9CD98CF9441E@microsoft.com...
> Can someone help me in developing a formula that will:
> 1.  Count how many merged cells in a column that are created from 2 cells
> AND contain both text and numbers?
> 2.  Count how many merged cells in a column that are created from 3 cells
> AND contain both text and numbers?
> 3.  Council how may merged cells in a column that are blank. 


0
David
4/9/2010 4:01:53 PM
The formulas just to count cells with either text or numbers (or text AND 
numbers) is bad enough, but I don't even know of a worksheet function that 
can tell you:
1) if a cell is merged or not
2) how many cells are merged to make the one troublemaker

My advice is to go back and take the time to unmerge the cells and use 
"center across selection" for the horizontal alignment of them across however 
many columns are required.  Merging of cells that will be used in 
calculations or referenced in VB code = BAD.

"Excel Ella" wrote:

> Can someone help me in developing a formula that will:
> 1.  Count how many merged cells in a column that are created from 2 cells 
> AND contain both text and numbers?
> 2.  Count how many merged cells in a column that are created from 3 cells 
> AND contain both text and numbers?
> 3.  Council how may merged cells in a column that are blank.
0
Utf
4/9/2010 9:28:01 PM
Reply:

Similar Artilces:

total number of characters in a cell
Is there a function that can count the total number of characters in a cell? Thanks The LEN function will return the total number of characters in a cell. E.g., =LEN(A1) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "plumstone" <plumstone@discussions.microsoft.com> wrote in message news:6F6D1E31-CF16-4167-8F1A-B1F5152FD515@microsoft.com... > Is there a function that can count the total number of characters in a cell? > Thanks LEN() Note that this includes space and other non-printing characters. In article ...

Remove . (period) from merged data
I have a Word 2003 doc that merges data from our database, including a ref no which contains a period as part of its format (e.g. 10.001). We now need to remove the period from the ref no once merged in the Word doc, as another company who receive the document (shipping company) are having problems with it. They need it to be shown as 10001. We cannot/do not want to re-format the ref no in the database itself, as it serves a useful purpose for us. Is there a way to "code out" the period once the ref has arrived in the Word doc, perhaps by using a numeric picture switch o...

Cells get automatically converted
I want to store installment details of employees in the format: 9/15, 10/15 etc.. These are text values and are transferred via .NET application to Excel. But it considers it a date and so converts 9/15 to 15-Sep, 10/15 to 15-Oct. I even searched for options to disable this, but could not find it. Please help switching this off. In message 1190896904.756552.247560@y42g2000hsy.googlegroups.com, RP <rpk.general@gmail.com> Proclaimed from the tallest tower: > I want to store installment details of employees in the format: 9/15, > 10/15 etc.. These are text values and are transferre...

Grouping like coloured cells
I have a conditional format set up to change cells different colours when certain conditions are met. I want to be able now to set up a separate table with all the same coloured cells in. I'm sure this is possible, however, I also want to be able to copy the row title into a section of the new table. Is there a way to do this? I have just changed the format of the chart. All I need to do now is form three seperate charts of positives, negatives and neutrals, however, they still need to be linked to the original row title. ...

Insert Worksheet Name into a cell
Hi, I've been trying in vain to solve this problem. I work with multiple worksheets and part of my title has th worksheet's name. Right now, for each title, i edit the title manually. Is there any function like the = cell("filename") that extracts th filename that can work on extracting the sheet name instead.? Thanks in advance. Priti -- Message posted from http://www.ExcelForum.com Hi Priti Provided the workbook has been saved, you could use =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99) -- Regards Roger Govier "Pr...

Format all cells containing formulas
I'd like all cells which contain calculated values to show up in different color (or some other formatting change). I know I can toggl formula fields to be visible and manually update each one individually but I'd ideally like something that automatically formats new formul cells (and even applies to new .XLSs and worksheets). Seems like a obvious thing to want to do, but haven't found this anywhere in th UI. A usable fallback option would be a macro to search the worksheet an apply (eg) Red Bold to every cell containing a formula. Any pointers? Thanks, - -- Message posted fr...

use command button to total datas in cells through VBA codes in ex
how can i establish a new command button in combo box to total datas in cells by wrinting VBA codes ?? thanks you ! Roy ...

Drop Down Boxes for Validation Cells are Too Wide
For some reason, in some columns of my spreadsheet the validation drop down boxes are too wide for the cell. In column A, for example, they will be the width of the cell plus the dropdown arrow - perfect. In column C they will exceed the width of the cell plus the drop down arrow. How do I fix this? Extra wide dropdown lists appear occasionally, usually if cells on the worksheet have been merged (not necessarily the cell with the data validation). To get rid of them, you may have to delete the row or column and recreate the data validation. Geoff wrote: > For some reason, in some...

I'm trying to merge 2 workbooks into 1 new workbook.
I need assistance on merging 2 workbooks in to 1. Information may or maynot be different in each workbook. Can this be done?? ...

Access Databases merging when syncing PDAs with Handbase
Help................. I have a probelm with seperate Access databases merging when PDA's are sync'd back to handbase. I have created a a handbase database created with Data Exchange to get the data into it. I then have a blank Acess database where the table etc etc match the Handbase database. I then out it onto several PDA's and data is collected. Then the PDA's are docked and the data updates in the Acess database.....all good !!! The work is carried out in serveal areas so I make a new database for each area........the probelm is thugh sometimes when a PDA is docked all...

Error Message when sending mail merge
I keep getting this error message when attempting to send a mail merge e-mail from one of my campaigns. "You do not have enough privileges to access the Microsoft CRM object or perform the requuested operation. For more information, contact your system administrator. Do you want to send the e-mail? If you clik Yes, the e-mail will be sent out, but no corresponding activity will be created in Microsoft CRM." I defintley want this activy to be recorded, any ideas? This is really strange. I have three contact inmy marketing list, I get this message only for the first two and ...

protecting cells (Excel 2000)
How do I prevent certain cells from being altered, allowing users to enter data only in certain areas. I can only see how to protect an entire sheet or workbook. Cheers David Kinston melbourne.au Hi first select the cells for which you want allow entries. Goto Format - Cells - Protection and uncheck 'Locked'. Now protect the sheet -- Regards Frank Kabel Frankfurt, Germany "David Kinston" <a@b.c> schrieb im Newsbeitrag news:Otkx#SewEHA.2568@TK2MSFTNGP11.phx.gbl... > How do I prevent certain cells from being altered, allowing > users to enter data only in cert...

cell color #4
is it possible to auto change cell color depending whether there is a yes or no in the cell...i want to make the cell turn red when i enter yes cheers Keith read about Conditional Formatting http://www.mvps.org/dmcritchie/excel/condfmt.htm =LEFT(A1)="y" or if spelled out =A1="yes" --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "kd" <keith.g0vcl@btinternet.com> wrote in messa...

moving cells to new sheets
I am trying to tidy up a fairly large and complex workbook that is all (quite confusingly) kept on one sheet. Is there any way to move cells to other worksheets and have them hold on to their original reference from the original sheet? e.g. easily copy cell from Sheet 1 with say, (=R1/2) to Sheet2 so it now has the formula (='Sheet1'!R1/2) Thanks David Try: Cut (from Sheet 1) > Paste (in Sheet 2) D Abramovich <youdontneed@myemail.add> wrote in message news:0a4b01c38f05$65235a50$a401280a@phx.gbl... > > I am trying to tidy up a fairly large and complex workbook ...

Adding Formula to Cell with Data
Column A has hard coded data. I need to add to the hard coded data figure another cell. I do not want to insert a new column or change the format of my worksheet. How can this be done without manually adjusting each cell? =datanumber+CellAddress as in =123+B2 GatorGirl wrote: > Column A has hard coded data. I need to add to the hard coded data figure > another cell. I do not want to insert a new column or change the format of > my worksheet. How can this be done without manually adjusting each cell? > > What type of data in the cells? If numbers and you want ...

Cell Character Display Limit
I have numerous cells with over 3500 characters in them. In one case, only about 2000 characters are displayed. Is there some way to get all the characters to display. Must I format the cells a specific way? Thanks, Barb Reinhardt Place ALT+Enter periodically in the text, say every hundred characters or so. Barb Reinhardt wrote: > I have numerous cells with over 3500 characters in them. In one case, only > about 2000 characters are displayed. Is there some way to get all the > characters to display. Must I format the cells a specific way? > > Thanks, > Bar...

Anchoring cells
I have a spreadsheet I am working on where I have 4 columns of demographic information followed by 12 columns for their status during specific months. I would like to anchor the 4 columns and then be able to go the month in question. I want to be able to see the demo information next to the cell for that person and that month. HOw? Xcel 2004 for Mac if you need the information. Thanks for any help. -- I want to find a voracious, small-minded predator and name it after the IRS. Robert Bakker, paleontologist Hi Kurt Place your cursor in cell E2>Windows>Freeze Panes This w...

Extract data from a cell after a constant value
Howdy All, I have a spreadsheet that contains 1 column of data, if the cells of the column are the city names and populations of towns in the following format: Appleton, Wisconsin (WI) (pop. 70,087) The 'pop.' is always present. I want to extract just the population number (ie: 70,087) from these cells into another cell. What is the simplest way to accomplish this? Thank you, Brian On Sun, 19 Aug 2007 12:29:37 -0500, "Wolfman" <wolfmanx@charter.net> wrote: >Howdy All, > >I have a spreadsheet that contains 1 column of data, if the cells of the >...

linking cells from different files -fill down
I'm trying to fill the same formula (links) down a column on worksheet. I'm linking from the same cell from a months worth of files,only th file reference is changing as shown below.(11-1-05,11-2-05,etc..) Cell A1='C:\Reports\Nov\[11-1-05.xls]Production'!$Z$1 CellA2='C:\Reports\Nov\[11-2-05.xls]Production'!$Z$1 CellA3 ='C:\Reports\Nov\[11-3-05.xls]Production'!$Z$1 Thanks, Gre -- singlgl ----------------------------------------------------------------------- singlgl1's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2638 ...

Print multiple mail merge results on each postcard page in Publish
After creating a postcard in Publisher and successfully completing a mail merge, I only seem to be able to print one "addressed" post card per page. When I choose multiple copies (4 per page), I get the same addressee 4 times. Is it possbile to print 4 mail merge results on a single page, to minimize paper waste? Are you actually printing the cards or relying on what print preview is telling you? Print preview shows all the cards the same, it is a bug in the program. For a mail merge to be successful you can only have one card on your screen. -- Mary Sauer MSFT MVP http://...

how can i susbstitute the symboals like '-' or��#�� in the cell?
hi, till now ,the excel only support substitute the text in the cell,as we can use SUBSTITUE function. But if i want to substitue the symboals like ��-�� or��#'��how can i do? Regars, Chelsea On Thu, 13 Dec 2007 09:12:47 +0800, "Sebation" <ec.wangqian@gmail.com> wrote: >hi, >till now ,the excel only support substitute the text in the cell,as we can >use SUBSTITUE function. >But if i want to substitue the symboals like ��-�� or��#'��how can i do? > >Regars, >Chelsea > What was wrong with using the SUBSTITUTE function? e.g. =SUBSTITUTE(...

Count Instances
How can I count the number of times each of numerous items appears in a column? That is, I have a column a a b b b c c c c d d etc. Each entry represented by a letter above is in fact several words. There are about 10,000 rows in the table. I would like result like a 2 b 3 c 4 d 2 etc. Thanks in advance. Ken You can use the countif function, say for example you wanted to find the number of occurances of the letter a in the column (which is column A) =COUNTIF(A1:A10000,"a") If you have many entries you could have a table of the occurances you are looking for and inste...

Formating of cell size-
Hi- It should be simple but I can t figure it out. Cells are locked to size from top to bottom of document .Yes I can adjust the heights and widths but how do I format some cells in the bottom of a document that I dont want to be the same size as ones a little higher in the document. ? Thanks Scott Scott Row heights and column widths are the properties of the entire rows and columns. IF you will not use the sheet for anything but appearance only I could suggest using the "merge cells" feature. I hesitate to make this suggestion due to the problems created by merged cells whe...

How to change a color of a cell using a condition formula
I would like to change the color of a cell automatically using a condition formula.....is this possible? Yes, go to Format | Conditional Formatting. -- Regards Juan Pablo Gonz�lez "Carlos" <Carlos@discussions.microsoft.com> wrote in message news:1330539A-8B3B-44DE-AF70-1E686D172379@microsoft.com... > I would like to change the color of a cell automatically using a condition > formula.....is this possible? Hi see 'Format - Conditional Format' -- Regards Frank Kabel Frankfurt, Germany Carlos wrote: > I would like to change the color of a cell automat...

Rounding formula won't copy to other cells in column
I have a formula in cell G2 that reads: =round(F2,0). I'm using this to round the value in cell F2 and remove decimal places. The entire "F" column has values I want to round, and I am unable to "fill" the formula so that I can get the rounded values of each record in the range. What should I be looking for? So what *does* happen when you copy the formula in G2 to other cells in the column? Check to make sure Calculation is set to Automatic (Tools/Options/Calculation). In article <1933354D-C4AE-4051-87BD-29AF728A57DA@microsoft.com>, LindaO <Linda...