Solved

Cell Splitting and count help in Excel

Posted on 2011-03-17
14
225 Views
Last Modified: 2012-05-11
I have a list of documents copy and pasted from our website parent directory placed into an Excel spreadsheet.

All of the info has gone into column A

I need the total count of the size column Document-List.xls
0
Comment
Question by:auraorange
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst
Ingeborg Hawighorst earned 125 total points
ID: 35155004
Hello,

there was really no need to post a 2.5 MB document. A few dozen rows would have been sufficient. We're not all on unlimited bandwidth and data plans.

The total COUNT of the Size column will be the same as the total count of the rows filled with document data.

Since your data is not structured in a regular way, you could do a text to column with the : as the delimiter. Then, on the resulting column B, do a text to column with the space as the delimiter.

Now you have the Size isolated in a column, but it still is text, because it has the K or MB qualifier.

Can you detail what you want to do with that? Do you want a total of the size of all items?

cheers, teylyn
0
 
LVL 33

Accepted Solution

by:
jppinto earned 125 total points
ID: 35155014
You can split the data into individual columns by going to the Data - Text to Columns Option. You didn't mentioned your Excel version...

After splitting the data, you want to count the size column? Please check the attached example. The count is in yellow cell, is 4574.

jppinto
Document-List.xls
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35155043
You can now use a formula to extract the byte value from the cell, taking into account that 1M = 1024 K

=IF(RIGHT(C7,1)="k",LEFT(C7,LEN(C7)-1)*1,LEFT(C7,LEN(C7)-1)*1024)

Copy down and sum.

See attached for an example with only 100 rows and all graphical objects removed. (76 KB!!!)

cheers, teylyn
Copy-of-Document-List.xls
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35155055
@jppinto, to count the rows from row 7 to the end, you don't need to split the data first.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35155120
Without re-organising the data you could put this formula in B7 copied down to get the file size in Mb

=IF(ISNUMBER(FIND("K  ",A7)),MID(A7,FIND("K  ",A7)-3,3)/1024,IF(ISNUMBER(FIND("M ",A7)),MID(A7,FIND("M  ",A7)-3,3)/1,0))

Now in B4 you can sum those with this formula

=SUM(B7:B5000)

I get a total of 887.78 Mb

regards, barry
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35155175
Barry,

far more elegant. I know this does not apply to the given dataset, but what about the possibility of a document being

13.5M

??
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35155217
"@jppinto, to count the rows from row 7 to the end, you don't need to split the data first. "

If you check my sample file, the data is already splitted...I've counted the data on the "size" column only.
0
 
LVL 2

Author Comment

by:auraorange
ID: 35155260
Using JPPINTO post / document attachment

I have got to 1089712 bytes

Just had to take out the K and M letters and convert the remaining e.g. 1.1m to 1100
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35155297
auroraorange,

1.1M is not = 1100

1M = 1024K

@jppinto, taking the original file and using

=COUNTA(A7:A4580)

arrives at exactly the same number, i.e. 4574

You don't have to split the columns to count the rows, because the Size column is filled in each row, anyway. So just count the rows without splitting. But apart from that, the objective seems to be a sum, not a count.

cheers, teylyn
0
 
LVL 33

Expert Comment

by:jppinto
ID: 35155316
Question title: "Cell Splitting and count help in Excel"

Question body: "I need the total count of the size column "

My original post question: "... you want to count the size column?"

That's why I asked if the user really wanted a count!!
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35155338
Exactly. That's why I suggested to "count of the rows filled with document data" in the first comment to this question.

Sometimes, askers may not word the question correctly, so experts will take a guess at what the asker really wants to achieve.

auroraorange's comment http:#a35155260 makes me think that it's really a sum, not a count, that s/he is after.

cheers, teylyn
0
 
LVL 2

Author Closing Comment

by:auraorange
ID: 35155353
Thank you for all comments and time taken to help.

Apologies for putting count rather than sum in the original comment
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35155459
>I have got to 1089712 bytes

was that the final result for the data you posted? If you are counting in bytes then I suggest the real total is approximately 9 times larger

regards, barry
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35155502
auroraorange,

if you were after a Sum, why did you accept the Count as the answer?

As far as I'm concerned, barryhoudini with http:#a35155120 should get the points for the most elegant solution for a sum based on your data. If you wanted a count, then the first comment to your question, i.e. http:#a35155260  should be the solution.

cheers, teylyn
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question