Search for a string and extract it?

[benbernards]

Hey guys,
So I'm guessing this will be simple:

I have large blocks of text inside a single cell. I want to search
through the text and extract a single string, as such:

(Contents of a Cell)

blah blah
blah blah
Disk Size: 50 GB <-- I'd like to pull out just "50" and put it in its
own cell
blah blah
blah blah

I'd like to do this via a vba function on a spreadsheet that would
have lots of cells of such text blocks -- so the end result is I have
a list of Disk Sizes that I can then total up.

Any ideas?

 

[JE McGimpsey]

One way:

Public Function DiskSize(ByVal sINPUT As String) As Double
Const sSEARCH As String = "Disk Size:"
Dim dResult As Double
Dim nPos As Long
Dim i As Long
nPos = InStr(UCase(sINPUT), UCase(sSEARCH))
If nPos > 0 Then
sINPUT = Trim(Mid(sINPUT, nPos + Len(sSEARCH)))
For i = 1 To Len(sINPUT)
If Not IsNumeric(Mid(sINPUT, i, 1)) Then Exit For
Next i
sINPUT = Left(sINPUT, i - 1)
If IsNumeric(sINPUT) Then _
DiskSize = CDbl(sINPUT)
End If
End Function

 

[Joel]

Are all the filesizes in GB? You plan will not work if you have combination
of bytes, KB,MB, and GB

Sub getfilesize()


For Each cell In ActiveCell

numberstring = Mid(cell, InStr(cell, "Disk Size:") + 10)
Size = Val(numberstring)

Next cell

End Sub