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

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

1.52 MB

10.4 MB

1.06 GB

2.8 GB

Se attachment...

Is it possible to calculate these cells?

/Kasper

SizeReport.xlsx

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...

SizeReport.xlsx

=SUM(LEFT(A1:A89,FIND(" ",A1:A89)-1)*IF(RIGHT(A1:A

Enter by pressing CTRL+SHIFT+ENTER.

Kevin

@The_Barnman - =LEFT(A1,FIND(" ",A1)) gives an error dialog

See attached

SizeReport.xlsx

Try putting this

=IF(RIGHT(A1,2)="GB",VALUE

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

Kind regards

So equals

But as you can see there is the conversion of the various values to do too.

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

=LEFT(A1;FIND(" ";A1))

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 :-(

```
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
```

All Courses

From novice to tech pro — start learning today.

See attached.

Kevin

SizeReport.xlsx