Pull values after specific word

[biznez]

Hi, can someone please help with vba code where i pull date and account # from cell and put it in another cell.

For example if in column C contains "DATE IS 10-10-2015, ACCOUNT# 123456789"
I would like just the date in another column and account in another column. Dates and Account are not always in the same order. it can be a mixture. I think i would require something where if the words "DATE IS" is found in cell then pull the date and if the word "Account" is found then take the account # and put them in respective column A and B. Thanks for help

Result should be
Column A Column B
10-10-2015 123456789

[Richard Buttrey]

Hi,

Assuming the date strings are always 10 characters then in A1

=DATE(MID(C1,SEARCH("Date is",C1)+14,4),MID(C1,SEARCH("Date is",C1)+11,2),MID(C1,SEARCH("Date is",C1)+8,2))

and assuming the Account # is always 9 digits then in B1

MID(C1,SEARCH("#",C1)+2,9)

[JOHN H. DAVIS]

Maybe:

Sub biznezx()
Dim i As Long
Range("A2:A" & Range("A" & Rows.Count).End(3).row).TextToColumns Range("A2"), xlDelimited, xlDoubleQuote, False, True, False, True, False, True
For i = 2 To Range("A" & Rows.Count).End(3).row
    If Left(Cells(i, "A"), 4) = "ACCO" Then
        Cells(i, "B").Cut
        Cells(i, "A").Insert xlToRight
    End If
Next i
End Sub

[AlKey]

Enter formula in A1 and pull it to B1

=CHOOSE(COLUMNS($A:A),MID($C1,FIND("-",$C1)-2,10),MID($C1,SEARCH("#",$C1)+2,9))

Data Range

	A	B	C
1	10-10-2015	123456789	DATE IS 10-10-2015, ACCOUNT# 123456789

[biznez]

Thanks Guys but i was looking for something more like this. So far its working ok

    Dim c As Range

    Dim iCol As Integer

    iCol = 1
    For Each c In Range("A1", Range("A65536").End(xlUp))
        If InStrRev(c, "DATE IS") <> 0 Then
            iCol = iCol + 1
            Cells(1, iCol) = Mid(c, InStrRev(c, "DATE IS") + 8, 8)
            Cells(1, iCol + 1) = Mid(c, InStrRev(c, "ACCO") + 4, 10)
        End If
    Next c

[Richard Buttrey]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.