Getting the longest lengh in range of cells

I am trying to get the length of the longest cell in a range and use the 
array {=len(a1:a1)}. What happens is that it picks up the length of cell A1. 
Is there a way in a formula to determine the longest cell length in a range, 
or through VBA.

Thanks in advance 


0
gh3175 (18)
4/26/2005 5:51:20 AM
excel 39879 articles. 2 followers. Follow

1 Replies
539 Views

Similar Articles

[PageSpeed] 21

Hi
Try this formula array (please amend with last row number):
{=MAX(LEN(A1:A7))}

HTH
Cordially
Pascal


"Geoff" <gh@bob.com> a �crit dans le message de
news:uMKoCQiSFHA.1176@TK2MSFTNGP10.phx.gbl...
> I am trying to get the length of the longest cell in a range and use the
> array {=len(a1:a1)}. What happens is that it picks up the length of cell
A1.
> Is there a way in a formula to determine the longest cell length in a
range,
> or through VBA.
>
> Thanks in advance
>
>


0
papou
4/26/2005 7:41:37 AM
Reply:

Similar Artilces:

Formula with text and reference to a date cell
Hi all, I am wanting to the following in a cell: Week ending 31 Dec The "Week ending" will be text, but I want the date portion to read from another cell containing the date so that I don't need to update this cell each week. The source cell with the date in is formatted dddd, dd mmm. Is this possible? When I tried it, all I got was the date serial number. Thanks. Rob Hi With A1 as source cell ="Week ending " & TEXT(A1,"d MMM") -- When sending mail, use address arvil<at>tarkon.ee Arvi Laanemets <robin_gould_durban@hotmail.com> wr...

Can get Officex:mac to load
I've tried a couple times to load OfficeXformac from the CD onto my mac and I keep getting the same error message. "1008:22,-36 IO Error (Bad Disk)" and it always happens at the same file "advzip.dic" Can anyone help me around this? Had a similar problem? On 23/9/06 13:23, in article 1159014202.184064.97160@m73g2000cwd.googlegroups.com, "oneofftmc" <tm_cook@yahoo.com> wrote: > I've tried a couple times to load OfficeXformac from the CD onto my > mac and I > keep getting the same error message. "1008:22,-36 IO Error (Bad Disk)&quo...

how do i get a warning for repeated numbers #2
I am working with a spreedsheet incorporating invoice numbers. How do I create a warning when i repeat an invoice number I like to use conditional formatting when it's convenient: If you have invoice numbers starting in Cell A3, try conditional formatting from A3 through as far down column A as necessary with this: Formula is: =COUNTIF(A$3:A3,A3)>1 Format: Pattern: Bright Red Using that, the first entry of an invoice number is ignored, but all subsequent duplicates are flagged. Does that help? Regards, Ron "Kenya" wrote: > I am working with a spreedsheet incorpor...

cell text limits
Hi, There seems to be a limit to text in a cell. Does anyone know of a way to extend this, or get around it, or some other idea? Thanks! --Randy Starkey Randy Excel Help on "limits" or "specifications" reveals that Excel will allow 32,767 characters to be entered in a cell. However, it goes on to state that "only 1024 characters will be visible or can be printed" To work around this limitation, stick a few ALT + ENTERs in at appropriate spots, about every 100 characters.. The ALT + ENTER forces a line-feed and expands the 1024 limit. How far is...

COUNT # times text value occurrs in range
I want to count the number of times the values 'TRUE' & 'FALSE' occurr in a list I've used the following formula but it's returned the wrong value: =COUNTA(TRUE,or,FALSE,G7:G37) That returned the value: 34 but there are only 31 records in the lis which are TRUE or FALSE -- loscherlan ----------------------------------------------------------------------- loscherland's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=670 View this thread: http://www.excelforum.com/showthread.php?threadid=31871 Hi Loscherland use the Countif function...

how do I format a cell using zero as the first number?
I am entering numbers in an excel spread sheet and some of my numbers startwith a zero, which does not show up. How can I change that? Well this is an Access database forum; however, since Access has the same behavior, I'll take a stab: Numbers don't have leading zeros. If you need to see leading zeros, format it as a text field. In Access there are some fancy ways to display leading zeros in front of numerical characters, but then it's no longer a number. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builde...

Can I select cells 3 columns away from a value in a line
In column A I have several Names, In column D I have certain values. In a cell I want to collect the values of the line in column D if a certain name is on the same line in column A I know if I use sum I will get the total value, but is there a way automaticly to put the value there if the "name is in column A? col A col B col C col D col E col F Name Start End Hours Jack 9:00pm 3:00pm 6:00 John 10:00am 2:00pm 4:00 James 11:00am 5:00pm 6:00 Jack 9:00pm 3:00pm 6:00 John 10:00am 2:00pm 4:00 James 9:00am 1:00pm 4:00 Name Tot hours Jack 12...

Filtering data by date range (but excluding YEAR)
I have a bunch of dates. I just want to pull everything from January to March (1st quarter) but doesn't care about year. I will be selecting a Year from a combo box, filtering all the appropriate data. Then I want to look at all the dates and pick out only the ones from January to March. How do I do this? Or have I answered my own question already? Let me try this,thanks. Is there a good site I can read more about these date formats? On Aug 1, 6:08 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Add an output field in query design view like this --- > E...

Bug with cell merging in tables using Applescript
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Once you have merged cells in a table in Word 2008 using Applescript, any further reference to the table's cells in the script produce an error. <br><br>As an example, open Word 2008 and run this script: <br> tell application &quot;Microsoft Word&quot; <br> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;make new table at active document with properties {number of rows:4, number of columns:4} <br> &nbsp;&nbsp;&nbsp;&nbsp...

Ranking non-consecutive cells
First off...I'm not sure if rank is the correct term. I have three columns, B,E,G - each column contains a number, in column A I need it to input a number based on which of the three columns is greater. If B is greatest there would be a 1, E greatest = 2, etc. For example: If column E is the greatest number, then column A would have a 2. If anyone has any ideas on how to do this or can point me in the right direction I would appreciate it....Thanks. -- shikamikamoomoo ------------------------------------------------------------------------ shikamikamoomoo's Profile: http://www.e...

Getting project to recognize the current date for my formulas
Hi! Hope everyone had a great Thanksgiving break! Got a question about formulas. I use 2 that calculate both working days left and a status light. Working Days Left: IIf([% Work Complete]=100,0,ProjDateDiff([Finish],Date(),[Project Calendar])) Status Light: IIf([% Complete]=100,"Blue",IIf(ProjDateDiff([Current Date],[Finish],[Project Calendar])/480<0,"Red",IIf(ProjDateDiff([Current Date],[Finish],[Project Calendar])/480<5,"Yellow",IIf(ProjDateDiff([Current Date],[Finish],[Project Calendar])/480>=5,"Green")))) The probl...

Data in Cell dependant on drop down lists
I'm trying to create an idiot-proof spreadsheet for our family business staff wages: I have made a drop down list from January - December, and a second list from 2008 - 2019, these values form the headings of each subsequent page inc. staff payslips. I am able to select APRIL from a dropdown in cell B3; 2008 from a dropdown in CELL B5 ... BUT .... when those are selected I would like cell B7 to display "Pay Period 29/03/08 to 28/04/08" (or the appropriate Pay period for the month/year combination) So you have in B3: April, in B5: 2008. Assuming that additionally, the ...

master reformat cells to comma delimited format
I want to transfer info from an excel spreadsheet and need to convert the large spreadsheet to a comma delimited format. Thoughts? Thanks! ...

Linked Cells #8
How do I have a link jump to the linked cell vs. showing me the contents? Without knowing exactly what you're asking, you need to hyperlink the cell you want to jump from, select Bookmark, and then enter the Cell reference that you want to jump to. "Jill" <Jill@discussions.microsoft.com> wrote in message news:227A882B-189A-4BE3-AEAE-A5C0CF6EF751@microsoft.com... > How do I have a link jump to the linked cell vs. showing me the contents? ...

How to get the number of words in richedit?
Is any a method to get the number of words which exists in richedit control. I have search the msdn, and I just got the method which is used to get number of characters or bytes existed in richedit control. so is there any method or trick for got it. thanks :-) -- Frank F.Han +-----------------------------------------+ | winsays@:-)hotmail:-).com | +-----------------------------------------+ As far as I know, there is no trick. You'll need to write code that obtains the text and counts the words. -- Jonathan Wood SoftCircuits http://www.softcircuits.com Available for con...

Get records with a negative value anywhere in the table.
Hello Everyone, I have a table which has about 150 fields (columns). About 140 fields contain numbers. Note: This is not a normalized table as the data was imported from Excel. I need query or vba code to return a recordset where there are any negative numbers in any of the 140 fields. Can anyone point me in the right direction? Thanks, j.t.w "j.t.w" wrote in message news:74ff50c6-306b-43c4-b038-7530d3d56729@e7g2000yqf.googlegroups.com... > Hello Everyone, > > I have a table which has about 150 fields (columns). About 140 fields > contain numbers. Not...

Cell formatting #10
Hi I wonder if some one can help me. I have imported into Windows XP Profesional old spreadsheets fro Windows 98. The problem I am having is I am unable to format cell without decimals. Excel is changing numbers that I had without decimals in to decimals For example if I had 17 and I change that number to 18 it will show i as 0.18. I have tried formatting the cells from the format tool bu without any luck as even here it is showing me the general numbers wit 2 decimal points! Could it be something that was done when Windows XP was first loaded? Any help will be appreciate -- Message post...

I get error 554 whent I try to repair POP3 mail
I am not able to sen mail out of my POP3 account, when I try torepair it I get Error 554 on the "send" cicle of the repair process (sending test), anyone out there that has the same experience, Outlook 2007. Thanx wdl <wdl@discussions.microsoft.com> wrote: > I am not able to sen mail out of my POP3 account, when I try torepair > it I get Error 554 on the "send" cicle of the repair process (sending > test), anyone out there that has the same experience, Outlook 2007. > Thanx A 554 error is an error report from the mail server accepting the SMTP (outgo...

colour in a range
Hi i have an upper and lower values in columns B and C the user enters value in columns D to X what i want to do is check over a range from column D to X if any of the entered values are outside the ranges in columns B and C i want the cell to change colour(ie Red) if the vlaues are in sided the range i want the cell to turn green can anyone help with Vb code for this thanks in advance kevin Why not use conditional formatting, with a formula of say =D1=MAX(B1:C100) etc. -- HTH RP (remove nothere from the email address if mailing direct) "Kevin" <kevc...

Calculate next workday after adding calendar days to date in cell
I want to add 100 calendar days to a date in a cell and when that date falls on a weekend or holiday the formula will return the next workday vs. returning a weekend date. I tried the workday function but it counted 100 workdays not calendar days. Thanks in advance Darrell, One way. Holidays is a named range containing your holiday dates =A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0,2,1)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+100))))) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis th...

how do I get pictures from my computer to MicrosoftWord
I don't know how to get my pictures that I need to use from my pictures to Microsoft Word You need to know where your pictures are located first. Then, in Word, click Insert, Picture, From File, in the Insert Picture dialog box bring up the location of the file and click Insert. "Sweet Mama" wrote: > I don't know how to get my pictures that I need to use from my pictures to > Microsoft Word Do you know how to use a help file? "Sweet Mama" <Sweet Mama@discussions.microsoft.com> wrote in message news:65D8EDF0-6218-4142-A9DE-8271DA8A5...

how do i get my text expander to work in ms word?
Running Windows 7. My text expander (shorthand) will not work in Word. Suggestions? Go to the Shorthand site and make sure you are running the correct version that is compatible with Windows 7. Cheryl On Mar 27, 5:46=A0pm, Denise <Den...@discussions.microsoft.com> wrote: > Running Windows 7. My text expander (shorthand) will not work in Word. > Suggestions? ...

Select a formula / range from Excel drop down suggestions
As we enter a formula in a cell, Excel finds matches of what is being written comparing it to (all) formulas and ranges of the current workbook and displays them in a drop down box just beneath the cell. Can someone please tell me if there is a key combination that inserts the currently selected list item in the formula being written and save me the trouble of writing the whole thing by hand?? If you click the "fx" function button instead, then you can follow prompting that guides you through completing the formula. If you're typing a formula normally (not using t...

Insert horizontal line/single cell in Excel?
I want to insert a horizontal line inside a single cell (to divide). Am able to insert a diagonal line but not horizontal. Tried the presets for inside, but it divides multiple cells CLM Use the drawing toolbar to draw a line through the middle of the cell. Gord Dibben MS Excel MVP On Tue, 10 Jan 2006 11:01:07 -0800, CLM <CLM@discussions.microsoft.com> wrote: >I want to insert a horizontal line inside a single cell (to divide). Am able >to insert a diagonal line but not horizontal. Tried the presets for inside, >but it divides multiple cells ...

Cell Reference #4
I have created a formula to find the relative position of a cell base on certain criteria. I have then used the address function to create a cell reference. I want to then insert this reference into a formula to calculate a variance. The VAR function looks at the address formula as a formula and not a reference. My attempts at making this work have not gone well. Any suggestions? You can use the OFFSET function to return a reference based on your formula. -- David Hager Excel MVP "JC" <anonymous@discussions.microsoft.com> wrote in message news:2dbd701c394e7$2f0c57...