CODEWITHSUNDEEP
stringexcelvba
debal
debal

Reputation: 1017

Extract Part of String in Excel

I have a string in this manner

"Cars from Tokyo 5 Arrival in 33:53:03 hrs. at 13:56:39 Cars from Austria 5 Arrival in 33:53:07 hrs. at 13:56:43 Cars from India 5 Arrival in 33:53:03 hrs. at 13:56:39"

In the same cell at excel.

I need these parts of the string at 13:56:39 at 13:56:43 13:56:39 displayed at separate cells.

Please help

Upvotes: 0

Views: 1577

Answers (3)

ray
ray

Reputation: 8699

Down and dirty string manipulation:

Option Explicit

Sub Test()
    Dim cellValue As String
    Dim newCellArray() As String
    Dim valueYouWant As String
    Dim cellCounter As Integer
    Dim x As Integer
    Dim myRange As Range
    Const SEPERATOR_VALUE = "at "
    Const ASCII_A = 65

    For cellCounter = 1 To 10 '10 represents the last row...there are ways to set this dynamically if needed

        cellValue = Sheet1.Range("A" & cellCounter)
        newCellArray = Split(cellValue, "at ")

        'Array is zero-based, but we want to start at first split value
        For x = 1 To UBound(newCellArray)
            valueYouWant = Trim$(Left$(newCellArray(x), InStr(1, newCellArray(x), " "))) 'You could prefix this with "at " if you really needed it
            Sheet1.Range(Chr$(ASCII_A + x) & cellCounter).Value = valueYouWant
        Next x
    Next cellCounter

End Sub

Upvotes: 0

Jook
Jook

Reputation: 4682

I would have a different approach, using a formula:

enter image description here

Column B uses this formula:

B1=IFERROR(SEARCH("at ??:??:??",A$1,1),"")
B2=IFERROR(SEARCH("at ??:??:??",A$1,B1+11),"")

Column C uses this formula:

C1=IFERROR(PART(A$1,B1,11),"")

These would work for a large number of occurences.

Upvotes: 2

brettdj
brettdj

Reputation: 55682

If your data was in a single column I think a Regexp with a variant array would make sense.

But as a more flexible option You could use the following UDF as an array - entered formula to split the string

If your string was say in A1 and you expected a maximum of 5 matches

  1. Select B1:F1
  2. In the formula bar add =AtTime(A1)
  3. Press together

enter image description here

extra non-matches will have #N/A

code

updated to handle single matches 

 Function AtTime(strIN As String) As Variant
    Dim objRegex As Object
    Dim objRMC As Object
    Dim strOut() As String
    Dim lngCnt As Long
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .Pattern = "at \d{2}:\d{2}:\d{2}"
        If .test(strIN) Then
            Set objRMC = .Execute(strIN)
            If objRMC.Count > 1 Then
                ReDim strOut(1 To objRMC.Count)
                For lnGCnt = 1 To UBound(strOut)
                    strOut(lnGCnt) = objRMC(lnGCnt - 1)
                Next
            Else
                'handle single matches
                ReDim strOut(1 To 2)
                strOut(1) = objRMC(0).Value
                strOut(2) = "#N/A"
            End If
            AtTime = strOut
        Else
            AtTime = "no match"
        End If
    End With
End Function

Upvotes: 1

Related Questions