Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 wrote in message ps.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
Here is my attempt at it...
To get distance =============== =TRIM(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,9+SEARCH("D istance:",A1)),""),"miles","")) To get school name ================ =TRIM(MID(LEFT(A5,FIND("(",A5)-1),2,9999)) Rick wrote in message ps.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Aug 26, 1:20 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Here is my attempt at it... To get distance =============== =TRIM(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,9+SEARCH("D istance:",A1)),""),"miles","")) To get school name ================ =TRIM(MID(LEFT(A5,FIND("(",A5)-1),2,9999)) Rick wrote in message ps.com... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Aug 26, 2:30 pm, "Rick Rothstein \(MVP - VB\)"
wrote: 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 Rick, Thanks so much! Everything is working well! -Jon |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Sun, 26 Aug 2007 16:20:43 -0000, "
wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Sun, 26 Aug 2007 20:41:07 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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 Nice one liners, although the School formula returns the "»" » Atlantic City H.S. --ron |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
"Ron Rosenfeld" wrote in message ... On Sun, 26 Aug 2007 20:41:07 -0400, "Rick Rothstein \(MVP - VB\)" wrote: 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 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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Sun, 26 Aug 2007 21:24:18 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: "Ron Rosenfeld" wrote in message .. . On Sun, 26 Aug 2007 20:41:07 -0400, "Rick Rothstein \(MVP - VB\)" wrote: 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 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 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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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. 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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Sun, 26 Aug 2007 23:11:52 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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. 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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. 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. 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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
On Sun, 26 Aug 2007 23:46:18 -0400, "Rick Rothstein \(MVP - VB\)"
wrote: 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. 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. 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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Isolate Numerical Data in Cell
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 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 Well, in thinking about it, that may not be entirely true. I would think this should work... Function School(str As String) As String School = Trim(Split(Split(Application.WorksheetFunction. _ Clean(str), "(")(0), "»")(1)) End Function Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I isolate the data in a worksheet from the rest of the pa | Excel Worksheet Functions | |||
Cell Won't Accept Numerical Data | New Users to Excel | |||
converting numerical data in one cell to word data in another cell | Excel Worksheet Functions | |||
How do you isolate a graph from it's data? | Excel Discussion (Misc queries) | |||
isolate numbers in a cell | New Users to Excel |