How to retrieve desire text from input sheet to output sheet ?

I have two sheet (input & output). please check the attachment.
input sheet having data in the CELL A1:A , & form them i want to retrieve desire text in the output sheet A:E.

Try

Option Explicit

Sub test()
    Dim a, i As Long, n As Long
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        a = .Resize(, 4).Value
    End With
    For i = 1 To UBound(a, 1)
        If a(i, 1) Like "MO*" Then
            n = n + 1: a(n, 1) = Split(a(i + 1, 1))(0)
        ElseIf a(i, 1) Like "STATE*" Then
            a(n, 2) = Split(a(i + 1, 1))(0)
        ElseIf a(i, 1) Like "*FAULT CODES*" Then
            a(n, 4) = Split(a(i + 1, 1))(0)
            a(n, 3) = Trim$(Mid$(a(i, 1), InStrRev(a(i, 1), " ")))
        End If
    Next
    With Range("e1").Resize(, 4)
        .Value = [{"MO","STATE","MO TYPE","FAULT CLASS","FAULT CODE"}]
        .Rows(2).Resize(n).Value = a
        .CurrentRegion.Columns.AutoFit
        .CurrentRegion.Columns("c:d").HorizontalAlignment = xlCenter
    End With
End Sub

Hi, jindon
Outstanding performance. Your macro is very speedy which is appreciated. Now my desire job almost 99.99% ok. it will be very helpful if the desire output I get in the “output” sheet, it is required because after retrieving desire fault code then I will be vlookup from sheet “faultsheet” so that I will get the meaning of actual fault code. Please check the attachment.
In your output results: “MO TYPE” is missing , other than all are ok..

MO TYPE
CF
TRX
TS
TS
TS
TS
TX
TS
TRX
TS
TX
TS

Hi, jindon
Just Great. My requirement is 100% fulfillment. The speed of your macro is extremely first which was unexpected !!. now I am very much happy to get the macro. I will be more happy if I can implement vlookup macro in between Output sheet & faultlist sheet . if you have time then you can help me , other then I will Do my job by formula.Then it will look like worst as partial macro & partial formula. I think you understand me ..take care..

"Output" sheet will be like below

MO	     STATE     MO TYPE  FAULT CLASS	   FAULT CODE   FAULT TEXT
RXOCF-201  OPER	     CF           2A	                 26	         Climate Sensor Fault

above red marks is done by vlookup from below sheet "faultlist"

MO TYPE	FAULT CLASS FAULT CODE	Faut Description
CF	2A	       26                 Climate Sensor Fault

Hi, mc84excel
Thanks for your time, your idea & my idea are same & that is done by vlookup. But it is just slow as I have lots of data to work . & for this I am going to use jindon’s macro as its speed is extremely fast !!

Originally Posted by nur2544

I will be more happy if I can implement vlookup macro in between Output sheet & faultlist sheet .

Missed that one.
All in one.
Change to

Option Explicit

Sub findfault()
    Dim a, b, i As Long, ii As Long, n As Long, txt As String, e
    With Sheets("input")
        a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(, 5).Value
    End With
    ReDim Preserve a(1 To UBound(a, 1), 1 To 6)
    For i = 1 To UBound(a, 1)
        If a(i, 1) Like "MO*" Then
            n = n + 1: a(n, 1) = Split(a(i + 1, 1))(0)
            a(n, 3) = GetMOType(a(i + 1, 1))
        ElseIf a(i, 1) Like "STATE*" Then
            a(n, 2) = Split(a(i + 1, 1))(0)
        ElseIf a(i, 1) Like "*FAULT CODES*" Then
            a(n, 5) = a(i + 1, 1)
            a(n, 4) = Trim$(Mid$(a(i, 1), InStrRev(a(i, 1), " ")))
        End If
    Next
    b = Sheets("faultlist").Cells(1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 2 To UBound(b, 1)
            For ii = 3 To 5
                txt = Join$(Array(b(i, 1), b(i, 2), b(i, 3)), Chr(2))
                .Item(txt) = b(i, 4)
            Next
        Next
        For i = 1 To n
            For Each e In Split(Application.Trim(a(i, 5)))
                txt = Join$(Array(a(i, 3), a(i, 4), e), Chr(2))
                a(i, 6) = a(i, 6) & IIf(a(i, 6) <> "", " - ", "") & .Item(txt)
            Next
        Next
    End With
    With Sheets("output").Range("a1").Resize(, 6)
        .CurrentRegion.ClearContents
        .Value = [{"MO","STATE","MO TYPE","FAULT CLASS","FAULT CODE","FAUT TEXT"}]
        .Rows(2).Resize(n).Value = a
        .CurrentRegion.Columns.AutoFit
        .CurrentRegion.Columns("c:d").HorizontalAlignment = xlCenter
    End With
End Sub

Function GetMOType(ByVal txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Pattern = "\S{3}([^\-]+)(?=\-)"
        GetMOType = .Execute(txt)(0).submatches(0)
    End With
End Function

Hi, jindon
Brilliant job, Just outstanding performance. every day I would spend more than one hour to make the fault report, but your macro takes only few second to get the report & which one is the 100 % accurate. I am really very happy to get the wonderful macro. Thanks you very much for saving my valuable time. Keep faith on me , yes obviously I will not forget your help….
Take care & wishing you very healthy life….