Isolate Numerical Data in Cell

[jtreble]

Hello,

After using a web query from Mapquest, I found that the Distance (in
miles) between the two locations is imported along with the text.
The cell reads:

"Total Est. Time: 1 hour, 57 minutes Total Est. Distance: 100.38
miles"

Can someone please explain how I can isolate the number of miles
( 100.38 ) into its own cell using VBA?

Also, another huge web query displays the names of NJ schools in this
format:

"» Atlantic City H.S. (01-0110-010)" (Yes the "»" is always included
at the beginning). Anyway to isolate the text here to just yield
( Atlantic City H.S.)?

Any help would be greatly appreciated.

Thanks,
Jon

 

[Bernard Liengme]

This gets the 100.38:
=--MID(SUBSTITUTE(A1," miles",""),FIND(":",A1,FIND(":",A1)+1)+2,256)

This gets the school name
=LEFT(RIGHT(A2,LEN(A2)-2),FIND("(",A2)-4)

Convert them to VBA if you wish
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

Hello,

After using a web query from Mapquest, I found that the Distance (in
miles) between the two locations is imported along with the text.
The cell reads:

"Total Est. Time: 1 hour, 57 minutes Total Est. Distance: 100.38
miles"

Can someone please explain how I can isolate the number of miles
( 100.38 ) into its own cell using VBA?

Also, another huge web query displays the names of NJ schools in this
format:

"» Atlantic City H.S. (01-0110-010)" (Yes the "»" is always included
at the beginning). Anyway to isolate the text here to just yield
( Atlantic City H.S.)?

Any help would be greatly appreciated.

Thanks,
Jon

 

[Rick Rothstein \(MVP - VB\)]

Here is my attempt at it...

To get distance
===============
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,9+SEARCH("Distance:",A1)),""),"miles",""))

To get school name
================
=TRIM(MID(LEFT(A5,FIND("(",A5)-1),2,9999))

Rick


Hello,

After using a web query from Mapquest, I found that the Distance (in
miles) between the two locations is imported along with the text.
The cell reads:

"Total Est. Time: 1 hour, 57 minutes Total Est. Distance: 100.38
miles"

Can someone please explain how I can isolate the number of miles
( 100.38 ) into its own cell using VBA?

Also, another huge web query displays the names of NJ schools in this
format:

"» Atlantic City H.S. (01-0110-010)" (Yes the "»" is always included
at the beginning). Anyway to isolate the text here to just yield
( Atlantic City H.S.)?

Any help would be greatly appreciated.

Thanks,
Jon

 

[jtreble]

Here is my attempt at it...

To get distance
===============
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,9+SEARCH("Distance:",A1)),""),"miles",""))

To get school name
================
=TRIM(MID(LEFT(A5,FIND("(",A5)-1),2,9999))

Rick


Hello,

After using a web query from Mapquest, I found that the Distance (in
miles) between the two locations is imported along with the text.
The cell reads:

"Total Est. Time: 1 hour, 57 minutes Total Est. Distance: 100.38
miles"

Can someone please explain how I can isolate the number of miles
( 100.38 ) into its own cell using VBA?

Also, another huge web query displays the names of NJ schools in this
format:

"» Atlantic City H.S. (01-0110-010)" (Yes the "»" is always included
at the beginning). Anyway to isolate the text here to just yield
( Atlantic City H.S.)?

Any help would be greatly appreciated.

Thanks,
Jon

Rick and Bernard,

Thanks so much for your help! This is going to save a lot of time for
me.

Would anyone know how to also isolate the three numbers in the "»
Atlantic City H.S. (01-0110-010)" to yield three difference cells
containing the numbers?

I tried modifying one of your scripts to do that but got stuck.

For example,

"» Atlantic City H.S. (01-0110-010)" would yield one cell with ( 01 ),
another cell with ( 0110) and a third cell with ( 010 ).

The character length of the high school name will vary by hundreds of
records of schools, but the digits of these three numbers will remain
constant.

Any help would be greatly appreciated!

Thanks,
Jon

 

[Rick Rothstein \(MVP - VB\)]

Would anyone know how to also isolate the three numbers in the "»

Atlantic City H.S. (01-0110-010)" to yield three difference cells
containing the numbers?

Try these...

1st Number
=================
=SUBSTITUTE(LEFT(A5,FIND("-",A5)-1),LEFT(A5,FIND("(",A5)),"")

2nd Number
=================
=SUBSTITUTE(LEFT(A5,FIND("-",A5,FIND("-",A5)+1)-1),LEFT(A5,FIND("-",A5)),"")

3rd Number
=================
=SUBSTITUTE(LEFT(A5,FIND(")",A5)-1),LEFT(A5,FIND("-",A5,FIND("-",A5)+1)),"")

Rick

 

[jtreble]

Try these...

1st Number
=================
=SUBSTITUTE(LEFT(A5,FIND("-",A5)-1),LEFT(A5,FIND("(",A5)),"")

2nd Number
=================
=SUBSTITUTE(LEFT(A5,FIND("-",A5,FIND("-",A5)+1)-1),LEFT(A5,FIND("-",A5)),"")

3rd Number
=================
=SUBSTITUTE(LEFT(A5,FIND(")",A5)-1),LEFT(A5,FIND("-",A5,FIND("-",A5)+1)),"")

Rick

Rick,

Thanks so much!

Everything is working well!

-Jon

 

[Bill Renaud]

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.)

 

[Ron Rosenfeld]

Hello,

After using a web query from Mapquest, I found that the Distance (in
miles) between the two locations is imported along with the text.
The cell reads:

"Total Est. Time: 1 hour, 57 minutes Total Est. Distance: 100.38
miles"

Can someone please explain how I can isolate the number of miles
( 100.38 ) into its own cell using VBA?

Also, another huge web query displays the names of NJ schools in this
format:

"» Atlantic City H.S. (01-0110-010)" (Yes the "»" is always included
at the beginning). Anyway to isolate the text here to just yield
( Atlantic City H.S.)?

Any help would be greatly appreciated.

Thanks,
Jon

Here is another VBA solution for both of your questions, since that is what you
asked for.

It is actually three different VBA functions. They could be done as two
functions, and more generally applicable, but I chose to write them as specific
for your problem.

In particular, you could make them more general by making "Pattern" an argument
instead of hardcoded into the function. But you should probably read about
Regular Expressions first.

As written:

=Distance(cell_ref)

will return the first floating point number after the word Distance: in your
string

=School(cell-ref)

will return the string that begins with a letter or number and ends at the
series of <space>'s that is followed by the "(".

=Schoolnums(cell_ref, Index)

will return the numbers after that first "(" in three different groups. Set
Index=1 for the first; Index =2 for the second; Index = 3 for the third.

Enjoy.

==============================================
Option Explicit
Function Distance(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
With re
.IgnoreCase = True
.MultiLine = True
.Global = True
.Pattern = "[\s\S]+Distance:.(\b\d*\.?\d+\b)[\s\S]+"
End With
Distance = re.Replace(str, "$1")
End Function
'-----------------------------------------------
Function School(str As String) As String
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\w[\s\S]*?(?=\s\()"
If re.test(str) = True Then
Set mc = re.Execute(str)
School = mc(0)
End If
End Function
'-----------------------------------------------------
Function SchoolNums(str As String, Index As Long)
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\((\d+)-(\d+)-(\d+)"
If re.test(str) = True Then
Set mc = re.Execute(str)
SchoolNums = mc(0).submatches(Index - 1)
End If
End Function
====================================================


--ron

 

[Rick Rothstein \(MVP - VB\)]

Option Explicit

Function Distance(str As String)
Dim re As Object
Set re = CreateObject("vbscript.regexp")
With re
.IgnoreCase = True
.MultiLine = True
.Global = True
.Pattern = "[\s\S]+Distance:.(\b\d*\.?\d+\b)[\s\S]+"
End With
Distance = re.Replace(str, "$1")
End Function

Since we know the OP has a regional setting where the "dot" is the decimal
point...

Function Distance(str As String)
Distance = Val(Split(str, "Distance:", , vbTextCompare)(1))
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

Function School(str As String) As String

Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\w[\s\S]*?(?=\s\()"
If re.test(str) = True Then
Set mc = re.Execute(str)
School = mc(0)
End If
End Function

Since we we know the school text always starts with » then....

Function School(str As String) As String
School = Trim(Mid(Split(str & "(", "(")(0), 2))
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

Function SchoolNums(str As String, Index As Long)

Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "\((\d+)-(\d+)-(\d+)"
If re.test(str) = True Then
Set mc = re.Execute(str)
SchoolNums = mc(0).submatches(Index - 1)
End If
End Function

And the final one-liner....

Function SchoolNums(str As String, Index As Long)
SchoolNums = Split(Mid(Replace(str, ")", "-"), _
InStr(str, "(") + 1), "-")(Index - 1)
End Function

Rick

 

[Ron Rosenfeld]

And the final one-liner....

Function SchoolNums(str As String, Index As Long)
SchoolNums = Split(Mid(Replace(str, ")", "-"), _
InStr(str, "(") + 1), "-")(Index - 1)
End Function

Rick

Nice one liners, although the School formula returns the "»"

» Atlantic City H.S.


--ron

 

[Rick Rothstein \(MVP - VB\)]

Function School(str As String) As String

Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\w[\s\S]*?(?=\s\()"
If re.test(str) = True Then
Set mc = re.Execute(str)
School = mc(0)
End If
End Function

Since we we know the school text always starts with » then....

Function School(str As String) As String
School = Trim(Mid(Split(str & "(", "(")(0), 2))
End Function

The above function assumed the school string started with the » character.
But, on the off-chance the string has the quote marks too...

Function School(str As String) As String
School = Trim(Split(Split(str, "(")(0), "»")(1))
End Function

Rick

 

[Rick Rothstein \(MVP - VB\)]

Nice one liners,

Thanks! It is kind of what I am "famous" for back in the compiled VB
newsgroups.

although the School formula returns the "»"

Not if the string doesn't have the quote marks around it. But fair enough...
it could. I just posted this correction back in the School function
subthread...

Function School(str As String) As String
School = Trim(Split(Split(str, "(")(0), "»")(1))
End Function

Rick

 

[Ron Rosenfeld]

Thanks! It is kind of what I am "famous" for back in the compiled VB
newsgroups.


Not if the string doesn't have the quote marks around it. But fair enough...
it could. I just posted this correction back in the School function
subthread...

Function School(str As String) As String
School = Trim(Split(Split(str, "(")(0), "»")(1))
End Function

Rick

OK, That's better. BUT, if the school name happens to have a Line Feed
character following (alt-Enter) prior to the "(", then the intervening spaces
and LF get returned also.

Mine does that also, if there is a space starting the next line, and I can make
a small change in the pattern to allow multiple whitespace characters in the
lookahead:

original:
re.Pattern = "\w[\s\S]*?(?=\s\()"

Modified:
re.Pattern = "\w[\s\S]*?(?=\s+\()"

or

=====================
Function School(str As String) As String
Dim re As Object
Dim mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\w[\s\S]*?(?=\s+\()"
If re.test(str) = True Then
Set mc = re.Execute(str)
School = mc(0)
End If
End Function
================================
--ron

 

[Rick Rothstein \(MVP - VB\)]

Function School(str As String) As String

OK, That's better. BUT, if the school name happens to have a Line Feed
character following (alt-Enter) prior to the "(", then the intervening
spaces
and LF get returned also.

I am not following you on this. Can you post a sample text string
demonstrating this problem? Maybe include symbols where you want me to
perform some kind of keyboard action (and explain that action).

Rick

 

[Ron Rosenfeld]

I am not following you on this. Can you post a sample text string
demonstrating this problem? Maybe include symbols where you want me to
perform some kind of keyboard action (and explain that action).

Rick

The text string may depend on how your reader renders it.

"» Atlantic City H.S.
(01-0110-010)"" (Yes the ""»"" is always included
at the beginning). "

In the above, there is a LF after the first line. Your formula (and my
original) returns it leaving a small box at the end. I can't reproduce it
here, but the ASCII Codes would be:

65 | 116 | 108 | 97 | 110 | 116 | 105 | 99 | 32 | 67 | 105 | 116 | 121 | 32 |
72 | 46 | 83 | 46 | 32 | 10 |


Note the space (32) and LF (10) codes at the end.

--ron

 

[Rick Rothstein \(MVP - VB\)]

Function School(str As String) As String

The text string may depend on how your reader renders it.

"» Atlantic City H.S.
(01-0110-010)"" (Yes the ""»"" is always included
at the beginning). "

In the above, there is a LF after the first line. Your formula (and my
original) returns it leaving a small box at the end. I can't reproduce it
here, but the ASCII Codes would be:

65 | 116 | 108 | 97 | 110 | 116 | 105 | 99 | 32 | 67 | 105 | 116 | 121 |
32 |
72 | 46 | 83 | 46 | 32 | 10 |

Note the space (32) and LF (10) codes at the end.

Okay, I think I see what you are saying... there is a trailing space
returned for the condition you proposed. If I am seeing this problem
correctly, I believe this modification to my function will handle it...

Function School(str As String) As String
School = Trim(Split(Split(Replace(str, vbLf, ""), "(")(0), "»")(1))
End Function

Rick

 

[Ron Rosenfeld]

Okay, I think I see what you are saying... there is a trailing space
returned for the condition you proposed. If I am seeing this problem
correctly, I believe this modification to my function will handle it...

Function School(str As String) As String
School = Trim(Split(Split(Replace(str, vbLf, ""), "(")(0), "»")(1))
End Function

Rick

That seems to work for vbLF, which is something I can enter using XL. Since
this is being imported as part of a query, are there any other "white-space"
characters that might need to be accounted for?
--ron

 

[Rick Rothstein \(MVP - VB\)]

Okay, I think I see what you are saying... there is a trailing space

That seems to work for vbLF, which is something I can enter using XL.
Since
this is being imported as part of a query, are there any other
"white-space"
characters that might need to be accounted for?

Nope, no other generalized white space is possible... otherwise I won't be
able to create a one-liner to handle it.<g>

Rick