Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Go Back  Xtreme Visual Basic Talk > > > > Extracting specific information from a cell in EXCEL


Reply
 
Thread Tools Display Modes
  #1  
Old 05-09-2012, 09:46 AM
gman11 gman11 is offline
Newcomer
 
Join Date: May 2012
Posts: 2
Default Extracting specific information from a cell in EXCEL


Hi there, I'm a new user and can't figure out how to do this specific command on excel vba.

If I have a cell in excel containing information from a football match for example:

Goverla Zakarpatia 1 - 0 v Metalurg Z 51'



with "Goverla Zakarpatia" and "Metalurg Z" being the names of the teams,

"1-0" being the score

and "51'" being the duration of the game:

How do I create a function using vba which will give me, in a separate cell, just the score and the time separated with a space, in their exact formats, i.e. (1-0 51')

Thanks in advance for any help,

George
Reply With Quote
  #2  
Old 05-10-2012, 02:46 AM
Visvang's Avatar
Visvang Visvang is offline
Centurion
 
Join Date: Mar 2010
Posts: 119
Default Code

This is what i got maybe the experts can refine it

Code:
Sub num()
    Dim Str, Team1, Team2 As String  'declare varibales
    Dim Score1, Score2 As Integer   'declare varibales
    
    Score1 = 999    'set a triger
    
    Str = Sheet1.Cells(1, 1)    'get the origanal string
    
    For i = 1 To Len(Str)   'go thre every character
        If IsNumeric(Mid(Str, i, 1)) Then 'see if the current cahracter is a number
            If Score1 = 999 Then 'see if i already got score1
                Team1 = Mid(Str, 1, i - 2)  'get team1
                Score1 = Mid(Str, i, InStr(i, Str, "-") - i - 1) 'get score1
                i = InStr(i, Str, "-") 'change i to team2 score incase team1 one score more than single digits
            Else: Score2 = Mid(Str, i, InStr(i, Str, " ") - i)  'get score2
                i = InStr(1, Str, " v ") 'set i  to get team2
                Exit For 'all values got so exit for to keep curren i
            End If
        End If
    Next i
    
    If Mid(Str, Len(Str) - 4, 1) = " " Then 'see if time played is in the time range 10 - 99
        Team2 = Mid(Str, i + 3, Len(Str) - i - 7) 'get team2
        Sheet1.Cells(1, 1) = Team1 & " v " & Team2  'change the value of org string
        Sheet1.Cells(1, 2) = Score1 & " - " & Score2 & " " & Mid(Str, Len(Str) - 3) 'layout the score as wanted
    Else: Team2 = Mid(Str, i + 3, Len(Str) - i - 8) '"extra time 100+" 'get team2
        Sheet1.Cells(1, 1) = Team1 & " v " & Team2 ''change the value of org string
        Sheet1.Cells(1, 2) = Score1 & " - " & Score2 & " " & Mid(Str, Len(Str) - 4) 'layout the score as wanted
    End If
    
End Sub
__________________
Whether you believe you can do a thing or not, you are right - Henry Ford

Record a macro if your not sure how things work!
Reply With Quote
  #3  
Old 05-11-2012, 11:31 AM
gman11 gman11 is offline
Newcomer
 
Join Date: May 2012
Posts: 2
Default

Thanks,

It's giving me an error on this line:

Else: Team2 = Mid(Str, i + 3, Len(Str) - i - 8) '"extra time 100+" 'get team2

Any ideas?
Reply With Quote
  #4  
Old 05-11-2012, 12:37 PM
DavidG DavidG is offline
Newcomer
 
Join Date: Dec 2003
Posts: 7
Default

Try this...
Code:
Private Sub ParseData()
    Dim iStringLen As Integer, iScore(1) As Integer, i As Integer, x As Integer
    Dim sTeam1 As String, sTeam2 As String, sDuration As String
    
    'This code can be modified for more efficient logic, works as is though.
    
    'Add 'Sheet1 and Sheet2
    'Place data in Column A 'Ukraine fussball'
    'FORMAT: Goverla Zakarpatia 1 - 0 v Metalurg Z 51'
    
    For i = 1 To 1 '<-- Change do loop or what any other loop
        iStringLen = InStr(1, Sheets("Sheet1").Cells(i, 1).Value, "-")
        For x = iStringLen To 1 Step -1
            If Mid(Sheets("Sheet1").Cells(i, 1).Value, x, 1) = " " Then
                If iStringLen - 1 = x Then
                Else
                    Exit For
                End If
            End If
        Next
        sTeam1 = Trim(Left(Sheets("Sheet1").Cells(i, 1).Value, x))
        iScore(0) = CInt(Trim(Mid(Sheets("Sheet1").Cells(i, 1).Value, x, iStringLen - x)))
        
        For x = iStringLen To Len(Sheets("Sheet1").Cells(i, 1).Value)
            Debug.Print Mid(Sheets("Sheet1").Cells(i, 1).Value, x, 1)
            If Mid(Sheets("Sheet1").Cells(i, 1).Value, x, 1) = " " Then
                If iStringLen + 1 = x Then
                Else
                    Exit For
                End If
            End If
        
        Next
        sTeam2 = Mid(Sheets("Sheet1").Cells(i, 1).Value, x, Len(Sheets("Sheet1").Cells(i, 1).Value) - x + 1)
        iScore(1) = CInt(Trim(Mid(Sheets("Sheet1").Cells(i, 1).Value, iStringLen + 1, x - iStringLen)))
        
        'Extract duration drom sTeam2
        For x = Len(sTeam2) To 1 Step -1
            If Mid(sTeam2, x, 1) = " " Then
                sDuration = Trim(Mid(sTeam2, x, Len(sTeam2) - x + 1))
                'if u want to remove apostrophe use this line (convert varible to integer)
                '''sDuration = Trim(Replace(Mid(sTeam2, x, Len(sTeam2) - x + 1), "'", ""))
                Exit For
            End If
            
        Next
        
        sTeam2 = Trim(Left(sTeam2, x))
        
        'if u want to replace 'v ' then use following line
        '''sTeam2 = Replace(sTeam2, "v ", "")
        
        'NOW PLACE INFO ON 2ND SHEET
        With Worksheets("Sheet2")
                .Cells(i, 1).Value = sTeam1
                .Cells(i, 2).Value = iScore(0)
                .Cells(i, 3).Value = iScore(1)
                .Cells(i, 4).Value = sTeam2
                .Cells(i, 5).Value = sDuration
        End With
        
    Next

End Sub

Last edited by Cerian Knight; 05-11-2012 at 01:43 PM. Reason: Added [code]...[/code] tags
Reply With Quote
  #5  
Old 05-11-2012, 02:40 PM
Gruff's Avatar
GruffExtracting specific information from a cell in EXCEL Gruff is offline
Bald Mountain Survivor

Retired Moderator
* Expert *
 
Join Date: Aug 2003
Location: Oregon, USA - deceased
Posts: 6,440
Default

gman,

Parsing text is not easy or sometimes impossible unless there are some given rules.

I note that your team names can have spaces in them. This makes it difficult to divide up your text by spaces.

In your sample you have spaces around your hyphant " 1 - 0 ".
Later in your sample you do not have spaces "1-0". Which is it?

Can your team names have hyphants in them?

If there is only one hyphant and there are no spaces around it then you could split your string by spaces and search for the one containing a hyphant. Then grab the last block of text and you have what you want.


Untested Code
Code:
Public Function GetScoreAndTime(sItem as string) as string ' Create and array of strings split by the space character Dim s() as string s = Split(sItem," ") ' Step through all sub strings and find the one that contains a hyphant. Dim Score as string Dim i as integer For i = 0 to uBound(s) If instr$(1,s(i),"-") > 0 then Score = s(i) Exit For End If Next i ' Get Last block of text GameTime = s(uBound(s)) GetScoreAndTime = Score & " " & GameTime Exit Function
__________________
Burn the land and boil the sea
You can't take the sky from me


~T

Last edited by Gruff; 05-11-2012 at 02:56 PM.
Reply With Quote
Reply

Tags
excel, extracting, function


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
 
Extracting specific information from a cell in EXCEL
Extracting specific information from a cell in EXCEL
 
-->