Extract Part of String in Excel

Question

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

Is the entire string in a single cell? Or are there three cells, each one having text beginning with Cars from? — user20416
– user20416, Commented Nov 12, 2012 at 9:52
no, the entire text is in the same cell, and i need each of those times in separate cells — debal
– debal, Commented Nov 12, 2012 at 10:00
is the text around the times always the same, or does it vary? If it varies, in which way? — Andreas Wallner
– Andreas Wallner, Commented Nov 12, 2012 at 10:02
Are there always three times in each cell? Can there be more? Can there be less? — user20416
– user20416, Commented Nov 12, 2012 at 10:02
it remains the same, for every instance of the time, there is an "at/b" (/b representing space as in C) preceeding it — debal
– debal, Commented Nov 12, 2012 at 10:03

Jook · Accepted Answer · 2012-11-12 13:04:49Z

2

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.

answered Nov 12, 2012 at 13:04

Jook

4,7523 gold badges31 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

brettdj · Accepted Answer · 2012-11-12 11:45:47Z

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

Select B1:F1
In the formula bar add =AtTime(A1)
Press shift ctrl enter 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

ray · Accepted Answer · 2012-11-12 17:56:38Z

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

Collectives™ on Stack Overflow

Extract Part of String in Excel

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related