Finding the cell with the most characters

Alright, fir one of my current projects, I need to output the contents
of the cell with the most text in it from a column. How would I do
this? I've tried arrays based on LEN, but I get the number of
characters instead of the cell contents.
0
arcandio (3)
12/15/2007 12:22:00 AM
excel 39879 articles. 2 followers. Follow

2 Replies
373 Views

Similar Articles

[PageSpeed] 56

On Fri, 14 Dec 2007 16:22:00 -0800 (PST), "arcandio@gmail.com"
<arcandio@gmail.com> wrote:

>Alright, fir one of my current projects, I need to output the contents
>of the cell with the most text in it from a column. How would I do
>this? I've tried arrays based on LEN, but I get the number of
>characters instead of the cell contents.

This *array* formula (enter with <ctrl><shift><enter>) should return the
contents of the cell with the most characters.  Rng refers to the appropriate
column (e.g. rng refers to: A1:A100).

=INDEX(rng,MATCH(MAX(LEN(rng)),LEN(rng),0))



--ron
0
ronrosenfeld (3122)
12/15/2007 1:41:24 AM
On Dec 14, 7:41 pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Fri, 14 Dec 2007 16:22:00 -0800 (PST), "arcan...@gmail.com"
>
> <arcan...@gmail.com> wrote:
> >Alright, fir one of my current projects, I need to output the contents
> >of the cell with the most text in it from a column. How would I do
> >this? I've tried arrays based on LEN, but I get the number of
> >characters instead of the cell contents.
>
> This *array* formula (enter with <ctrl><shift><enter>) should return the
> contents of the cell with the most characters.  Rng refers to the appropriate
> column (e.g. rng refers to: A1:A100).
>
> =INDEX(rng,MATCH(MAX(LEN(rng)),LEN(rng),0))
>
> --ron

Thanks very much Ron! I actually ended up solving the thing by adding
each cell to the one above it till it got to the top. This will help a
lot in other parts though!
0
arcandio (3)
12/15/2007 10:00:16 PM
Reply:

Similar Artilces:

Protect or Unprotect Cell dependent on different cell
I am trying to find a way of automatically protecting a cell if an adjacent cell has a specific value whilst if that cell has a different value the cell would be unprotected. For example if I am aiming to have a column with values in that will be entered but if a field next to it has a 1 in the value field will then be locked. But if the 1 is changed to a 0 the value field could then be edited. Many Thanks for any help. David You could do this with a worksheet_change event to make it fully automatic.Unlock ccell f1>protect the sheet>right click sheet tab>view ...

Changing font colour in some cells
Hi I am quite new to Excel! I can change the appearance of text using bold, italic and underline but am having problems with colour. When I select an active cell and click on a colour nothing seems to happen. If I go back to the cell and double click it shows the text in the colour I want but when I move to another cell it reverts to black. Can anyone help please? Does this happen with all workbooks or just one? If the latter, maybe there is some event code behind the sheet. On the tab name, right-click and select View Code from the menu, if you see some code delete it. -- HTH Bob P...

Can I lock a set of cells in a worksheet?
Hi, Is it possible that a range of cells in a worksheet is protected from user editing, and the rest of the worksheet is editable as usual ? Puneet Arora -- puneetarora_12 ------------------------------------------------------------------------ puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18406 View this thread: http://www.excelforum.com/showthread.php?threadid=385468 Try this sequence .. Select the entire sheet first (press CTRL+A) Click Format > Cells > Protection tab > Uncheck "Locked" > OK Now select the range ...

Editing Cell's in Excell 03
I have a question. I use excell to create a CSV file so I can upload auctions at yahoo auctions. I have already a set of descriptions (since the description is one basic one for all auctions) listed in my database sheet. I want to change them, however when I select a sell the window pops down and shows ALL the description. I want to just copy the new description into all the cells at he same time...but it's not working properly. Does anyone know how I can get it to now show the whole cell information so I can copy the new description and paste the same information into all the descripti...

Finding Macros In Workbooks
Is there a wasy to find all Excel workbooks in a folder that have a macro in them. If so, how? Thanks, John ...

Show Numeric Cell Value Instead of ####
I'd like to have a narrow column of numeric data extend/flow into the adjacent right hand column, instead of showing ###. ie |12|455| |11|3 |76|888| Instead of |##| | |##| | |##| | Can't find anything in Cell or Column formating to accomplish this. Help please! Maybe you can merge the two cells. But be aware that merging cells can cause lots of trouble later on--sorting, copy|pasting.... Rox123456 wrote: > > I'd like to have a narrow column of numeric data extend/flow into the > adjacent right hand column, instead of showing ###. > > ie > &g...

How do I return which row the active cell is in?
Before I start, thanks to all the people who've already helped me on this board. Solely because of you, I've managed to complete 10% of a simple but large project (2 applications so far). I've hunted the boards now for a couple of hours and haven't found the answer to a very simple issue. What I want to do is this: I have a worksheet with data already on it. I want to cut and paste data from another worksheet. I need to identify the first empty row at the end of the data and return its row number. I tried the following. Sub test() Dim lRowNum As Long Range("A655...

Run a macro based on the value of a cell
Hi Everyone I have what seems to be a simple task but can't seem to figure it out. I have a excel file that has the tabs "Current User List" & "Prev User List" On these sheets the first row is called "status" which is either "curr" or "prev" to indicate employee status. I copied and modified the macro posted on this site for copying an entire row to another worsheet and it works fine but I'd like to go one step further. If I change the status of an employee on the "Current User List" to "Prev" I want ...

Is there a way to hide text in a cell?
I'm trying to format a spreadsheet where some information (passwords) should only be viewed by certain people.... not for the general viewer. Is there a way to hide text in a cell? I have done it in the past but I can't seem to figure this out now. IT For a very casual and inexperienced user you may get away with something like this, but anything you do can be easily un-done by just about anyone who could access these news groups. You could format the text to white so's it wouldn't show in the cell. You can go to Format>Cell>Protection and check "hidden"...

Cell returns worksheet name
Hi. There's got to be a function for this. How can I get a cell to return the name of the current worksheet? This would be ideal for groupeding worksheets, creating your work, and then, once ungrouped, having the sheet name apear in a cell on that particular sheet. Thank you for any help on this. "Derick" <gittyup777@yahoo.com> wrote in message news:SgUkc.1215$a47.573@newsread3.news.atl.earthlink.net... > Hi. > > There's got to be a function for this. > > How can I get a cell to return the name of the current worksheet? > > This would be ide...

Finding last row in a column
Column A contains entries of any formats including blanks. My objective is to find the last blank row# in Column A, and post it in cell D1. Can you help me with a formula to do that? Thanks I expect you haven't described your question correctly. The last blank row in column A is most likely the last row on the worksheet, row 65536. Do you mean the last *embedded* blank row? On Sun, 15 Aug 2004 02:33:41 GMT, "daniel chen" <danchen@worldnet.att.net> wrote: >Column A contains entries of any formats including blanks. >My objective is to find the last blank row# in C...

Move data in cells araound
Hi All, I have some data in an unfriendly format. It's organized like this: xxxyyyzzzaaabbbcccdddeeefff ggghhhiiijjjkkklllmmmnnn ooopppqqqrrr I'm looking for a way to reorganize it so it its more like this: xxx yyy zzz aaa bbb etc. I will be having to do this on a weekly basis. So I'm looking for a repeatable way to do this. Does anybody have a good suggestion on how I would accomplish this? Thanks Mike Is the data all in one cell in the format you show? Gord Dibben MS Excel MVP On Tue, 4 Mar 2008 17:01:01 -0800, mikebres <mikebres@discussions.microsoft.com> wro...

Custom list limited to 255 characters? (XL 2007)
I have a client who claims that she could create longer custom lists in Excel 2003 compared to the limit of 255 characters that she managed to Exceed when trying to regain her customizations in the newly installed Office. It seems as if she is right? Anyone else who had this problem or can point me towards a better solution? -- Best regards Malin D Office consultant/Trainer EXCEL 2007 Hit F1 to launch the F1 Help Facility Type in:- EXCEL Specifications And Limits - in the Search box then hit Search. Inspect the topic. Ctrl-F to lauch Find then, in the find...

count occurences of a character in a colume
Im sure there is an easy way, I cant find it though. How could I count all cells in a colume where the value was X this is excel 2007 thanks Craig =countif(a:a,"x") is one way. Debra Dalgleish has a bunch of "counting" function descriptions here: http://contextures.com/xlFunctions04.html And a bunch more notes for "sum" functions here: http://contextures.com/xlFunctions01.html MSNews wrote: > > Im sure there is an easy way, I cant find it though. > How could I count all cells in a colume where the value was X > this is excel 2007 > > t...

Excel (Office 2007 B2TR) -- Password Protecting A Sheet -- Character Limit ?
I have used really long passwords in Excel (Office 2007 BETA). My passwords are longer than 32 characters but not more than 64 characters. Two issues with B2TR seem to have occurred: 1.) password length now limited? 2.) cannot paste password into 'Confirm Password' dialog? Here's how I have been protecting Sheets (not Workbook): With the sheet I wish to protect in front and visible, I select 'Review' Then I select 'Protect Sheet' 'Protect Sheet' dialog appears. I can paste my really long password. Click [OK]. 'Confirm Password' dialog appear...

Can't add a 0 (zero) to the beginning of a number in a cell
I am trying to put a serial number in a cell but it starts with a zero. After I type in 09867453 and move to the next cell the 0 disappears. Scott You can format the cell to be text and it will then allow a 0 to be added to the front of a number however this may cause problems if you are using that number in inequalities. Scottrow wrote: > I am trying to put a serial number in a cell but it starts with a zero. > After I type in 09867453 and move to the next cell the 0 disappears. > > Scott Scott, The first answer is correct however it may be alot easier if you add an a...

Range name title appearing as background in merged cells
Hi, Cell E11 is a Named Range. When i Merge E11:H11 the text in Cell E11 is centred across the Merged area which is great, however, i can see the Range Name i assigned to E11 also centred across the Merged area. Would anyone know how i can get rid of this please? Thanks John ...

Funny Characters on emails
I faced the same problem last year but renstalled Exchange=20 5.5 from square one. It's again here with me. Isn't there=20 a solution? Well, When I send out emails, the recipient=20 receives Garbage! Funny characters at the end of exchange=20 or at the Beginning! Most of them talk about mime setup=20 and unsupported Mime format! Please see below What I'm=20 talking about! I have applied and reapplied the service=20 packs in vain! Last year, I was running exchange 5.5 on=20 Windows NT 4 Server now I decided to push it to Windows=20 2000 server since my friends told me my NT server&...

Date Cell In Chart Title?
How do I keep a date cell value in the dd/mm/yyyy format vs. it converting back to a serial number? The Chart Title refers to a cell with the date in the correct format but when I concatenate it into the title it reverts back to a serial number. Thank you ...

Hide Rows if cell value is
I would like to hide a row if certain values are entered in three cells. For e.g. if United Kingdom is selected in Cell C3 and C5 and CI is selected from cell C10, I would then have Row 16 hidden. I would like this to be dynamically i.e. updated whenever the value in the cell changes. Many thanks in advance. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200605/1 Hi Mohd, Try: '=============>> Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Me.Range("C3,C5,C10") If Not Intersect(rng, Ta...

Summing non consecutive cells
I am attempting to add every other cell in a 958 row set of numbers. For instance: =SUM (F5+F7+F9+F11+F13......F957) and then I want to add all the even number rows, =SUM (F6+F8+F10+F12+F14......F958). Any suggestions other than typing this formula out. Thanks Use two helper columns (column G and H for instance) and put the formula =IF(MOD(CELL("row",F5),2)=0,F5,0) in Column G for even rows and use formula =IF(MOD(CELL("row",F5),1)=0,F5,0) in Column H for odd rows. Gary "csfrolich" wrote: > I am attempting to add every other cell in a 958 row set of n...

Not plotting blank cells in a chart.
I am trying to show a plotted line graph in Excel. If I have example data in cells A1 – A5, and then make a plotted line graph from source data A1-A80. I would like the graph to ONLY show the data plotted in A1-A5, until I add data in A6. Once I add data in A6, A7, A8, etc, I would like the graph to only plot the data that has values filled in the cells. Right now if I have source data from A1-A80, and actual values only in A1-A5, it will plot the values in A1-A5, and then my line goes to 0 for A6-A80. I am looking to have the graph only chart the cells with values in them. I am l...

how to edit cell
how to minimze cell in access This is an Excel usergroup? "zila" wrote: > how to minimze cell in access ...

Macroless way to post dates in another cell
OK... So here's what I am trying to accomplish Should be simple but every time I try it I get an error... so I giv in. I need to get it set up so that when I enter any sort of value in cel E4 the date is automatically posted in cell I2. So simple yet beyond my comprehension -- Message posted from http://www.ExcelForum.com right click on the sheet tab>view code>insert this Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$E$4" Then range("e12")=date End Sub -- Don Guillett SalesAid Software donaldb@281.com "HoundofCullin&...

If functions and blank cells
Version: 2008 Operating System: Mac OS X 10.3 (Panther) Processor: Intel I have 2 columns of data with measurements that need to be converted. One column is in inches, the other is in centimeters. The bulk of the measurements are in inches, which means that most of the blank cells are in the centimeters column. However, a few of the centimeter cells have measurements. <br><br>How do I set up a conversion formula in the centimeter column without wiping out the information already there? <br><br>Basically I want to set it up to say something like: if f2 is blank then...