Split and change text

[taywl]

I have a list of codes like this in column B:
BC SB2 C08 illus brief BC SB2 C10 illus brief
BC SB3 C11 illus brief
BC SB2 C09 tech 1pf
BC SB2 C11 tech brief
AB SB2 C12 tech brief
AB RT3 C01 tech brief
AB SB3 C11 tech brief
AB RT6 C06 illus brief
ON SB6 C06 illus 2pf
ON SB6 C07 illus 3pf
ON SB6 C12 illus brief
ON SB6 C13 illus brief
ON SB6 C09 illus pfp
ON EN5 C08 tech 1pf

In column G, i want to display the first 2 characters (AB, BC, or ON).
In column H, i want to display the text after the last space. I also want to change 1pf, 2pf, 3pf... to "revision" and change pfp to "final". So column H will only display brief, revision, or final.
In column I, i want display the text after the 2nd last pace (illus or tech).

Thanks for your help in advance!

 

[mohadin]

Hi
Assume you data starts from B2
And first line By mistake have 2 columns
Try

Sub test()
    Dim b, x, c As Variant
    Dim i As Double
    b = Application.Transpose(Cells(2, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row - 1))
    ReDim c(1 To UBound(b))
    For i = 1 To UBound(b)
        x = Split(CStr(b(i)), " ")
        x = Application.Index(x, 0, Array(0, 5, 4))
        If x(2) Like "[1-9]pf" Then
            x(2) = "Revision"
        ElseIf x(2) = "pfp" Then x(2) = "final"
        End If
        c(i) = Array(x)
    Next
    For i = 1 To UBound(c)
        Cells(i, 2).Offset(1, 5).Resize(, 3) = c(i)(0)
    Next
End Sub

 

[jmacleary]

Assuming you data is EXACTLY as you have shown it, and you have mentioned all the different possibilities, then these formulea will do it:
Excel 2007 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]=LEFT(B1,2)[/td][td]=IF(ISERROR(FIND("pfp",B1,18)),IF(ISERROR(FIND("pf",B1,18)),"brief","revision"),"final")[/td][td]=TRIM(MID(B1,12,5))[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

If the data is more variable than you show, there are more complicated ways round.

 

[mohadin]

Another option

Sub test()
    Dim a As Variant
        b = Application.Transpose(Cells(2, 2).Resize(Cells(Rows.Count, 2).End(xlUp).Row - 1))
ReDim a(1 To UBound(b), 1 To 3)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "([A-Z]{2}\s)|((illus)|(tech))|((\dpf)|(brief)|(pfp))"
        For i = 1 To UBound(b)
        Set m = .Execute(b(i))
        a(i, 1) = m(0): a(i, 2) = m(2): a(i, 3) = m(1)
         If m(2) Like "[1-9]pf" Then
            a(i, 2) = "Revision"
        ElseIf m(2) = "pfp" Then a(i, 2) = "final"
        End If
        Next
    End With
     Cells(i, 2).Offset(1, 5).Resize(, 3) = a
End Sub

 

[DanteAmor]

Another aproch

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:171.09px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td >BC SB2 C08 illus brief</td><td >BC</td><td >brief</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td >BC SB2 C10 illus brief</td><td >BC</td><td >brief</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td >BC SB3 C11 illus brief</td><td >BC</td><td >brief</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td >BC SB2 C09 tech 1pf</td><td >BC</td><td >revision</td><td >tech</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td >BC SB2 C11 tech brief</td><td >BC</td><td >brief</td><td >tech</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td >AB SB2 C12 tech brief</td><td >AB</td><td >brief</td><td >tech</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td >AB RT3 C01 tech brief</td><td >AB</td><td >brief</td><td >tech</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td >AB SB3 C11 tech brief</td><td >AB</td><td >brief</td><td >tech</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td >AB RT6 C06 illus brief</td><td >AB</td><td >brief</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td >ON SB6 C06 illus 2pf</td><td >ON</td><td >revision</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td >ON SB6 C07 illus 3pf</td><td >ON</td><td >revision</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td >ON SB6 C12 illus brief</td><td >ON</td><td >brief</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td >ON SB6 C13 illus brief</td><td >ON</td><td >brief</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td >ON SB6 C09 illus pfp</td><td >ON</td><td >final</td><td >illus</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td >ON EN5 C08 tech 1pf</td><td >ON</td><td >revision</td><td >tech</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=LEFT(B2,2)</td></tr><tr><td >H2</td><td >=LOOKUP(RIGHT(B2,2),{"ef","fp","pf"},{"brief","final","revision"})</td></tr><tr><td >I2</td><td >=TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),99*3,99))</td></tr></table></td></tr></table>