Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calculate cells with letters

Posted on 2013-06-13
20
Medium Priority
?
261 Views
Last Modified: 2013-06-13
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
0
Comment
Question by:Kasper Katzmann
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +3
20 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39245502
Try using Val(A1)
0
 
LVL 24

Expert Comment

by:Steve
ID: 39245506
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39245546
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
Independent Software Vendors: 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!

 

Author Comment

by:Kasper Katzmann
ID: 39245549
@Martin - Val(A1) (or =Val(A1) returns #Name

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

Expert Comment

by:Steve
ID: 39245554
Ah Kevin... I was going to suggest something similar... but with VLOOKUP to simplify the values:

See attached
SizeReport.xlsx
0
 
LVL 10

Expert Comment

by:Gašper Kamenšek
ID: 39245560
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39245564
deleted....
0
 
LVL 24

Expert Comment

by:Steve
ID: 39245568
Kasper, try =-- not -=

So equals first

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

Expert Comment

by:NBVC
ID: 39245586
Not for points....

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

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

Author Comment

by:Kasper Katzmann
ID: 39245599
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39245605
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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 39245608
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
 

Author Comment

by:Kasper Katzmann
ID: 39245632
Sweet :-)
But now I don't know who to grant the prize :-)
0
 

Author Comment

by:Kasper Katzmann
ID: 39245642
And then I got it all wrong. It was you, Kevin, that should have the points. Sorry.
0
 

Author Comment

by:Kasper Katzmann
ID: 39245651
Yes, please
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39245657
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
 

Author Comment

by:Kasper Katzmann
ID: 39245731
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 39245749
Thanks, I just should have been quicker:)
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question