Solved

# Cell Splitting and count help in Excel

Posted on 2011-03-17
222 Views
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
Question by:auraorange
• 7
• 3
• 2
• +1

LVL 50

Assisted Solution

teylyn earned 125 total points
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

jppinto earned 125 total points
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

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

LVL 50

Expert Comment

@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

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

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

"@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

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

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

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

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

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

>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

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

#### Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!