?
Solved

Cell Splitting and count help in Excel

Posted on 2011-03-17
14
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
14 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 500 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 500 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
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50
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
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
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
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
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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

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