sum text column of common unit of measure numbers

users
#1

My cell phone provider only provides me a CSV with a text field for the usage.
I want to =sum( it so I need it to be numbers.
Is a macro the easiest way to make a new column of common unit of measure (GB?) numbers?

15 KB
5 KB
10 KB
15 KB
5 KB
1.8 MB
5 KB
15 KB
215 KB
130.2 MB
9.2 MB
475 KB
11.7 MB
10 MB
15 KB
12.1 MB
5 KB
10 KB
7.1 MB
5.3 MB
4.9 MB
15 KB
240 KB
130 MB
2.9 MB

#2

Hi James

My cell phone provider only provides me a CSV with a text field for the
usage.
I want to =sum( it so I need it to be numbers.
Is a macro the easiest way to make a new column of common unit of
measure (GB?) numbers?

15 KB
5 KB
10 KB
15 KB
5 KB
5 KB
5 KB
1.8 MB
5 KB
5 KB
15 KB
215 KB
130.2 MB
9.2 MB
475 KB
11.7 MB
10 MB
15 KB
12.1 MB
5 KB
10 KB
7.1 MB
5.3 MB
4.9 MB
15 KB
240 KB
130 MB
2.9 MB

I suggest the following procedure.
Open the *.TXT file in CALC, ticking "Separated by" and "Space" boxes. Should produce spreadsheet with Numeric values in Column A and Text (either MB or KB) in column B.
In Cell C1 put =IF(B1="MB",A1*1000,A1)
Copy Cell C1 down to bottom of table.
Save as *.odt file

Regards, MalJaros

#3

Correction
Last line should read
Save as *.ods file
Regards, MalJaros

#4

Copy your data into a txt file. Open with your
spreadsheet, separate by space to put numbers
in Col A and Kb/mb in Col b. Col c = A1*1000 to get
total kb. The sum up the whole thing. Divide the total
by 1000 to get mb.

#5

How can I have 2 IFs in case there are GBs?

#6

=if(B1="MB,A1*1000,A1*??)

You just change the final A1 to multiple by another number.

#7

Hi,
If your values takes up, for instance the range A2 to A29, you can
enter this formula as an array formula:
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))
The only trick is to hit CTRL-SHIFT-ENTER instead of just ENTER at the
end of the formula entry: if you only use ENTER, this will be a
"normal" formula and it will not work. To make sure you did it right,
the formula should appear with curly braces on each side in the formula
display, like this:
{=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))}
Another thing toi be careful about: if your decimal separator is the
comma (","), the dot in your list of numbers will also have to be
changed. You can change the formula like this to make the change of
separator:
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB",""),"
MB",""),".",",")))
Now, if you really want to be fancy, you can use the REGEX function to
remove the units. This will remove KB, MB, and GB:
=SUM(VALUE(REGEX(A2:A29," [KMG]B","")))
And don't forget that CTRL-SHIFT !
I hope this helps.
Rémy.

#8

Hi,
If your values takes up, for instance the range A2 to A29, you can
enter this formula as an array formula:
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))
The only trick is to hit CTRL-SHIFT-ENTER instead of just ENTER at the
end of the formula entry: if you only use ENTER, this will be a
"normal" formula and it will not work. To make sure you did it right,
the formula should appear with curly braces on each side in the formula
display, like this:
{=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB","")," MB","")))}
Another thing toi be careful about: if your decimal separator is the
comma (","), the dot in your list of numbers will also have to be
changed. You can change the formula like this to make the change of
separator:
=SUM(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2:A29," KB",""),"
MB",""),".",",")))
Now, if you really want to be fancy, you can use the REGEX function to
remove the units. This will remove KB, MB, and GB:
=SUM(VALUE(REGEX(A2:A29," [KMG]B","")))
And don't forget that CTRL-SHIFT !
I hope this helps.
Rémy.

#9

=IF(B1="kb",A3, IF(B1="mb",A1*1000,A1*1000000))

#10

Hello,
I just realised I messed up since I did not think about the
units.Considering:
1 GB = 1024 MB1 MB = 1024 KB
you can change the formula like this:
=SUM(IFERROR(VALUE(SUBSTITUTE(A2:A29,"
KB","")),0),IFERROR(VALUE(SUBSTITUE(A2:A29,"
MB",""));0)*1024,IFERROR(VALUE(SUBSTITUE(A2:A29,"
GB",""));0)*1024*1024)
This will give you the number of KB transferred. If you want the total
in MB, just divide the SUM by 1024.
I hope this helps,
Rémy.

#11

Hello,
I just realised I messed up since I did not think about the
units.Considering:
1 GB = 1024 MB1 MB = 1024 KB
you can change the formula like this:
=SUM(IFERROR(VALUE(SUBSTITUTE(A2:A29,"
KB","")),0),IFERROR(VALUE(SUBSTITUE(A2:A29,"
MB",""));0)*1024,IFERROR(VALUE(SUBSTITUE(A2:A29,"
GB",""));0)*1024*1024)
This will give you the number of KB transferred. If you want the total
in MB, just divide the SUM by 1024.
I hope this helps,
Rémy.

#12

This looks promising. :slight_smile:

#13

For some time now I have been unable to post to this list using gmane, so I decided to try subscribing to the list and using email. Hopefully this gets posted.

Regards, Jim

#14

https://en.wikipedia.org/wiki/Gmane
Use gmane.io

#15

I am using gmane.io. I switched to it when the original gmane stopped working. The problem started on the original gmane and continued to gmane.io. I have always been able to read the group but at some point in time I could no longer post to it.

At some point I corresponded with a moderator. He replied to my problem with some questions, I answered him but never heard anything back. At that point I just gave up.

I am sending this reply via gmane.io, we'll see what happens.

Regards, Jim

#16

I am using gmane.io. I switched to it when the original gmane stopped working. The problem started on the original gmane and continued to gmane.io. I have always been able to read the group but at some point in  time I could no longer post to it.

At some point I corresponded with a moderator. He replied to my problem with some questions, I answered him but never heard anything back. At that point I just gave up.

I am sending this reply via gmane.io, we'll see what happens.

Maybe you need to be subscribed.
I can't find a way to check my subscription.
You could leave yourself subscribed the "-nomail" way.