Calculate cells with letters

I have some data I would like to calculate, but my problem is that the cells contains letters. Like this:

1.52 MB
10.4 MB
1.06 GB
2.8 GB

Se attachment...

Is it possible to calculate these cells?


/Kasper
SizeReport.xlsx
Kasper KatzmannSeniorkonsulentAsked:
Who is Participating?
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
If you take an existing workbook in English and open it on a Danish system, Excel will convert the formulas into Danish.

See attached.

Kevin
SizeReport.xlsx
0
 
Martin LissOlder than dirtCommented:
Try using Val(A1)
0
 
SteveCommented:
You can convert to number in the column next:

=--LEFT(A1,FIND(" ",A1))

or you can use that in an array formula:
=SUM(--LEFT(A1:A89,FIND(" ",A1:A89)))
with [ctrl]+[shift]+[enter]

See attached

That just does the numbers:
something like the following may do the conversion too...
=IF(RIGHT(A1,2)="MB",--LEFT(A1,FIND(" ",A1))*1000,IF(RIGHT(A1,2)="GB",--LEFT(A1,FIND(" ",A1))*1000000,--LEFT(A1,FIND(" ",A1))))
SizeReport.xlsx
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
zorvek (Kevin Jones)ConsultantCommented:
This formula will return the total bytes:

=SUM(LEFT(A1:A89,FIND(" ",A1:A89)-1)*IF(RIGHT(A1:A89,2)="MB",1048576,IF(RIGHT(A1:A89,2)="GB",1073741824)))

Enter by pressing CTRL+SHIFT+ENTER.

Kevin
0
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
@Martin - Val(A1) (or =Val(A1) returns #Name

@The_Barnman - =LEFT(A1,FIND(" ",A1)) gives an error dialog
0
 
SteveCommented:
Ah Kevin... I was going to suggest something similar... but with VLOOKUP to simplify the values:

See attached
SizeReport.xlsx
0
 
Gašper KamenšekExcel MVPCommented:
Far bigger problem at my opinion is that you actually have apples, pears and strawberries (read GB, MB and KB) and you simply cannot add them together as one and the same so a bit more complex solution is needed...

Try putting this

=IF(RIGHT(A1,2)="GB",VALUE(LEFT(A1,FIND(" ",A1)-1))* 1048576,IF(RIGHT(A1,2)="MB",VALUE(LEFT(A1,FIND(" ",A1)-1))*1024,A1))

in a cell next to A1 and copying down... Now you get it all in KB

Kind regards
0
 
NBVCCommented:
deleted....
0
 
SteveCommented:
Kasper, try =-- not -=

So equals first

But as you can see there is the conversion of the various values to do too.
0
 
NBVCCommented:
Not for points....

the error can be because of regional settings... maybe replace commas with semicolons for your region.

=LEFT(A1;FIND(" ";A1))
0
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
Arghhh - damn

Is there a way to force excel to use the english formula instead of danish?

I hate the fact that excel uses different languages in the formulas according to the installation language. It makes it a bit difficult to google a solution when living in little Denmark :-(
0
 
Martin LissOlder than dirtCommented:
You can probably do what you want quite simply in a macro. Describe what it is that you want to do and I'll write it for you.
0
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
Sweet :-)
But now I don't know who to grant the prize :-)
0
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
And then I got it all wrong. It was you, Kevin, that should have the points. Sorry.
0
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
Yes, please
0
 
Martin LissOlder than dirtCommented:
For points or no points here's a macro that calculates the total GB for the column and puts it at the bottom of the column.
Sub GetTot()
Dim lngLastRow As Long
Dim lngRow As Long
Dim dblBytes As Double

lngLastRow = Range("A65536").End(xlUp).Row

For lngRow = 1 To lngLastRow
    Select Case True
        Case InStr(1, Cells(lngRow, 1).Value, "GB") > 0
            dblBytes = dblBytes + Val(Cells(lngRow, 1).Value) * 1073741824
        Case InStr(1, Cells(lngRow, 1).Value, "MB") > 0
            dblBytes = dblBytes + Val(Cells(lngRow, 1).Value) * 1048576
        Case InStr(1, Cells(lngRow, 1).Value, "KB") > 0
            dblBytes = dblBytes + Val(Cells(lngRow, 1).Value) * 1024
        Case Else
    End Select
Next

Cells(lngLastRow + 2, 1).Value = Format(dblBytes / 1073741824, "0.00") & " GB"
End Sub

Open in new window

0
 
Kasper KatzmannSeniorkonsulentAuthor Commented:
Wow - that is cool. I thougt you guys where incedible, but you just topped it with that one.

If I could give you both 500 points I'd do it.
0
 
Martin LissOlder than dirtCommented:
Thanks, I just should have been quicker:)
0
All Courses

From novice to tech pro — start learning today.