Solved

Cell Splitting and count help in Excel

Posted on 2011-03-17
14
224 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

786 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