• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Cell Splitting and count help in Excel

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
auraorange
Asked:
auraorange
  • 7
  • 3
  • 2
  • +1
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jppintoCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@jppinto, to count the rows from row 7 to the end, you don't need to split the data first.
0
 
barry houdiniCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jppintoCommented:
"@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
 
auraorangeAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
jppintoCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
auraorangeAuthor Commented:
Thank you for all comments and time taken to help.

Apologies for putting count rather than sum in the original comment
0
 
barry houdiniCommented:
>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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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

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.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now