Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Excel formula question


  • Please log in to reply

#1
diggeryo

diggeryo

    Member

  • Member
  • PipPipPip
  • 343 posts
I don't even know if this is possible, but I figured this would be the place to ask.

Is there a formula that will return the number of characters for the cell with the most characters in a column?

For example, if my column is:

a
aa
a
aaaa
a
aaaaa
a

The formula should return "5" since there are 5 characters in the cell with the most characters.

I know I can right-click on a column and choose "column width," but that number doesn't always equal the number of the most characters.

thanks,
mike.

Edited by diggeryo, 09 December 2009 - 11:17 AM.

  • 0

Advertisements


#2
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Don't know of a single formula that can do this, but you can do it in 2 steps.

If your characters are in column 'A', in column 'B', enter the formula:

=LEN(A1)

And copy it down to the end of your list

Then, anywhere else, put in the formula =MAX(B1:B???) where ??? = the last row of data

If you're used to using macros, this can be done quite simply
  • 0

#3
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
That just might work. Thanks for the suggestion!!
  • 0

#4
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
I have multiple lengths of data in about 11,000 cells in column V, ranging from lengths of 10 to 63.

I tried the following formula:

=Max(Len(V:V))

Now here's the wierd part--- the result of the formula that displays in the cell is the length of the cell in the same row I type the above formula into. HOWEVER, when I have the "Function Arguments" box up, it does show the correct result of 63 in the lower left corner of the box next to "Forumula result = "

Is there any way to get THAT result to show up in the cell?

By the way, I am using Excel 2007 SP2, fully updated.
  • 0

#5
diggeryo

diggeryo

    Member

  • Topic Starter
  • Member
  • PipPipPip
  • 343 posts
As soon as I clicked "add post" to my previous post, I figured it out. I have to enter that formula as an array: CTRL+SHIFT+ENTER while the cursor is in the formula bar.
  • 0

#6
Jonesey

Jonesey

    Member

  • Member
  • PipPipPip
  • 335 posts
Great thinking - I never thought about using an array formula!!
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP