I have entries in col G, formatted as General, containing text like the
following. (They represent the sizes of files. Only GB or MB. Always
three digits.)

Col G (text)
-------
3.40 GB
3.82 GB
22.8 MB


I want to place a formula or function or macro in col H which converts
col G to GB. (So that I can subsequently do addition on selected cells,
working only in GB.)

Col H (GB, numbers to 3 dp)
------
3.400
3.820
0.023

Struggling here, particularly with stripping out the GB or MP suffices,
so would much appreciate some help please.

Terry, East Grinstead, UK

I have entries in col G, formatted as General, containing text like the
following. (They represent the sizes of files. Only GB or MB. Always
three digits.)

Col G (text)
-------
3.40 GB
3.82 GB
22.8 MB


I want to place a formula or function or macro in col H which converts
col G to GB. (So that I can subsequently do addition on selected cells,
working only in GB.)

Col H (GB, numbers to 3 dp)
------
3.400
3.820
0.023

Struggling here, particularly with stripping out the GB or MP suffices,
so would much appreciate some help please.

Terry, East Grinstead, UK

How about using a helper col (set to display to 3 dec places) that converts the
numeric value conditional on IF the RIGHT 2 chars are "GB" then extract the
LEFT 4 chars, ELSE divide the LEFT 4 chars by 10K...

=IF(RIGHT($A1,2)="GB",LEFT($A1,4)/1,LEFT($A1,4)/1000)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Of course.., you'll need to change the $A1 ref to suit your sheet layout:

Assuming header row
=IF(RIGHT($G2,2)="GB",LEFT($G2,4)/1,LEFT($G2,4)/1000)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Of course.., you'll need to change the $A1 ref to suit your sheet layout:

Assuming header row
=IF(RIGHT($G2,2)="GB",LEFT($G2,4)/1,LEFT($G2,4)/1000)

Brilliant, thanks Garry, works a treat!

I'm wondering if it would be possible to avoid that 'helper' column?

https://www.dropbox.com/s/guvjbe1kg8...ula-1.jpg?dl=0

Or even go further and edit the VBA code (which I kindly received from
an Excel web forum) to deliver the output in that numerical format
directly? That's my preference after realising that I would sometimes
need to SUM various cells. To complete the picture, here's the code:

Public Function GetFileSize(rngcell As Range) As String
Const PROP_FILE As Long = 1
flAddress = rngcell.Hyperlinks(1).Address

With CreateObject("scripting.FileSystemobject")
pathName = .GetParentFolderName(flAddress)
flName = .GetFileName(flAddress)
End With

With CreateObject("Shell.Application").Namespace(pathNa me)
flSize = .GetDetailsOf(.Items.Item(flName), PROP_FILE)
End With
GetFileSize = flSize
End Function

Thanks again for the fast reply.

Terry, East Grinstead, UK

You can modify the function as follows and use it in col G to return numeric
value only. (Set col precision to 3dp)

Try...

Public Function GetFileSize(CellRef As Range) As Double
Dim sPath$, sFldr$, sFile$, vFldr, vSize, v1
Const File_Size& = 1

sPath = CellRef.Value
With CreateObject("scripting.FileSystemobject")
sFldr = .GetParentFolderName(sPath)
sFile = .GetFileName(sPath)
End With

Set vFldr = CreateObject("Shell.Application").Namespace("" & sFldr & "")
If vFldr Is Nothing Then Exit Function

With vFldr
For Each v1 In .Items
If v1.Name = sFile Then vSize = .GetDetailsOf(v1, File_Size): Exit For
Next 'v1
End With

v1 = Split(vSize, " ")
GetFileSize = IIf(v1(1) = "GB", CDbl(v1(0)) / 1, CDbl(v1(0)) / 1000)
Set vFldr = Nothing
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

You can modify the function as follows and use it in col G to return numeric
value only. (Set col precision to 3dp)

Try...

Public Function GetFileSize(CellRef As Range) As Double
Dim sPath$, sFldr$, sFile$, vFldr, vSize, v1
Const File_Size& = 1

sPath = CellRef.Value
With CreateObject("scripting.FileSystemobject")
sFldr = .GetParentFolderName(sPath)
sFile = .GetFileName(sPath)
End With

Set vFldr = CreateObject("Shell.Application").Namespace("" & sFldr & "")
If vFldr Is Nothing Then Exit Function

With vFldr
For Each v1 In .Items
If v1.Name = sFile Then vSize = .GetDetailsOf(v1, File_Size): Exit For
Next 'v1
End With

v1 = Split(vSize, " ")
GetFileSize = IIf(v1(1) = "GB", CDbl(v1(0)) / 1, CDbl(v1(0)) / 1000)
Set vFldr = Nothing
End Function

Thanks Garry, do appreciate your taking the time and trouble to work on
that. Unfortunately there appears to be something wrong. I've tested
carefully and think I see some pattern. My illustration will hopefully
help you to isolate the flaw:

https://www.dropbox.com/s/ma8cqc9qfp...ion-1.jpg?dl=0

Or
https://www.dropbox.com/s/ma8cqc9qfp...on-1.jpg?raw=1

The key point is that the original function works on the FULL hyperlink.
Yours seems to operate on the displayed text, which is typically just
the filename or subfolder(s) and filename. I tried adding an example
with the full link, and your edit does work OK on that.

BTW, the result was also formatted like the hyperlink in col F, namely
blue bold underlined.


Terry, East Grinstead, UK