Split and change text

taywl

New Member
Joined
Feb 2, 2012
Messages
14
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!
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi
Assume you data starts from B2
And first line By mistake have 2 columns
Try
Code:
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
 
Last edited:
Upvote 0
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.
 
Last edited:
Upvote 0
Another option
Code:
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
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top