Re: Isolate Numerical Data in Cell

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

This can be done with formulas, but I think formulas are actually more
cumbersome, and difficult to troubleshoot in this type of situation.
Since this is a programming newsgroup, you might be interested in my VBA
solution, which I tend to resort to:

Public Function GetString(SearchText As String, _
StartText As String, _
EndText As String) As String

Dim lngPos As Long
Dim lngStart As Long
Dim lngEnd As Long

On Error Resume Next

lngPos = InStr(1, SearchText, StartText, vbTextCompare)
If lngPos > 0 _
Then
lngStart = lngPos + Len(StartText)
lngEnd = InStr(lngStart, SearchText, EndText, vbTextCompare)
If lngEnd > 0 _
Then
GetString = Mid$(SearchText, lngStart, (lngEnd - lngStart))
Else
GetString = Mid$(SearchText, lngStart, (Len(SearchText) -
lngStart + 1))
End If
Else
GetString = ""
End If
End Function

Then, on your worksheet use the following formulas:
For the web query from MaqQuest example:
=VALUE(TRIM(GetString(A2,"Distance:","miles")))

For the names of NJ schools example:
=TRIM(GetString(A3,"»","("))

You could also include the space in the "StartText" and "EndText"
arguments to the GetString function and then eliminate the TRIM
worksheet function in the result, if you wanted.

(Note: To get the "»" character in the VBA editor, type Alt+0187. Use
the "Character Map" applet to find these.)
--
Regards,
Bill Renaud


.

Relevant Pages

  • RE: looping to run vb code -help
    ... Dim lngStart As Long ' assuming the OrderNo is a LongInteger ... Dim lngEnd As Long ... Dim lngCounter As Long ... If your OrderNo's are not incremental, you may want to insert some validation to make sure the current OrderNo exists. ...
    (microsoft.public.access.gettingstarted)
  • Re: Selecting Multiple Sheets in VBA
    ... 'Selects all sheets between two designated sheets. ... Dim objShts As Excel.Sheets ... Dim lngStart As Long ... Dim lngEnd As Long ...
    (microsoft.public.excel.worksheet.functions)
  • Re: How to have cursor focussed on center part of contents
    ... Dim colList As New Collection ... Dim lngPos As Long ... Dim lngStart As Long ... Dim lngLines As Integer ...
    (comp.databases.ms-access)
  • Re: selLength and no highlight
    ... Public Sub SpellCheck(ByRef txtCtrl As TextBox) ... txtCtrl.SelLength = lngPos - lngStart ...
    (comp.lang.basic.visual.misc)