Search for a string and extract it?

  • Thread starter Thread starter benbernards
  • Start date Start date
B

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?
 
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
 
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
 
Back
Top