Extract text from string (complex rules)

[hanmas]

Hi, can anyone please help me with this problem? It has been driving me crazy for days!

I have a column with data in the following (ideal) format:

texttextexttext/s2
texttextexttext/s2/c14
texttextexttext/s24/c2

and need a VBA solution to automatically split it across 3 columns, i.e

column1 column1 column1
texttextexttext s2
texttextexttext s2 c14
texttextexttext s24 c2

/s will always be present and will always be followed by one or more digits (no limit).
/c may or may not be present but if it is present, again it will always be followed by one or more digits (no limit)

I have been using TextToColumns with "/" as the delimiter and it works fine in most cases. However, the data frequently contains additional "/" characters which breaks everything. some examples of valid but problematic data are:

texttext3/12/2012text/s2/c2
text/textexttext/s2/c2

and even:
text/stext/ctext/s2/c2 (i.e. multiple occurrences of /s or /c)!!!

Please help!
Thanks,
Graham

 

[Andrew Poulsom]

In B1:

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-1),LEFT(A1,FIND("/",A1)-1))

In C1:

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,LEFT(SUBSTITUTE(A1,B1&"/",""),FIND("/",SUBSTITUTE(A1,B1&"/",""))-1),SUBSTITUTE(A1,B1&"/",""))

In D1:

=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,SUBSTITUTE(A1,B1&"/"&C1&"/",""),"")

copied down.

 

[hanmas]

Thanks very much Andrew, but I'm afraid this gives me the same result as TextToColumns with "/" as the delimiter. Items with additional "/" characters are still processed incorrectly. Some examples are:

PES 2011/SE 2011/S1
Split/Second/S1/C2
AC/DC LIVE*: Rock Band/S1/C3

Any ideas? All help much appreciated!
Thanks,
Graham

 

[Andrew Poulsom]

With your sample data I got:

<TABLE style="WIDTH: 322pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=429 x:str><COLGROUP><COL style="WIDTH: 127pt; mso-width-source: userset; mso-width-alt: 6180" width=169><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 127pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=169>texttextexttext/s2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=132 x:fmla='=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-1),LEFT(A1,FIND("/",A1)-1))'>texttextexttext</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 x:fmla='=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,LEFT(SUBSTITUTE(A1,B1&"/",""),FIND("/",SUBSTITUTE(A1,B1&"/",""))-1),SUBSTITUTE(A1,B1&"/",""))'>s2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=64 x:str="" x:fmla='=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,SUBSTITUTE(A1,B1&"/"&C1&"/",""),"")'></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>texttextexttext/s2/c14</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))>1,LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1))-1),LEFT(A2,FIND("/",A2)-1))'>texttextexttext</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))>1,LEFT(SUBSTITUTE(A2,B2&"/",""),FIND("/",SUBSTITUTE(A2,B2&"/",""))-1),SUBSTITUTE(A2,B2&"/",""))'>s2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))>1,SUBSTITUTE(A2,B2&"/"&C2&"/",""),"")'>c14</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>texttextexttext/s24/c2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A3)-LEN(SUBSTITUTE(A3,"/","")))>1,LEFT(A3,FIND(CHAR(1),SUBSTITUTE(A3,"/",CHAR(1),LEN(A3)-LEN(SUBSTITUTE(A3,"/",""))-1))-1),LEFT(A3,FIND("/",A3)-1))'>texttextexttext</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A3)-LEN(SUBSTITUTE(A3,"/","")))>1,LEFT(SUBSTITUTE(A3,B3&"/",""),FIND("/",SUBSTITUTE(A3,B3&"/",""))-1),SUBSTITUTE(A3,B3&"/",""))'>s24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A3)-LEN(SUBSTITUTE(A3,"/","")))>1,SUBSTITUTE(A3,B3&"/"&C3&"/",""),"")'>c2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>texttext3/12/2012text/s2/c2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))>1,LEFT(A4,FIND(CHAR(1),SUBSTITUTE(A4,"/",CHAR(1),LEN(A4)-LEN(SUBSTITUTE(A4,"/",""))-1))-1),LEFT(A4,FIND("/",A4)-1))'>texttext3/12/2012text</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))>1,LEFT(SUBSTITUTE(A4,B4&"/",""),FIND("/",SUBSTITUTE(A4,B4&"/",""))-1),SUBSTITUTE(A4,B4&"/",""))'>s2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A4)-LEN(SUBSTITUTE(A4,"/","")))>1,SUBSTITUTE(A4,B4&"/"&C4&"/",""),"")'>c2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17>text/textexttext/s2/c2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A5)-LEN(SUBSTITUTE(A5,"/","")))>1,LEFT(A5,FIND(CHAR(1),SUBSTITUTE(A5,"/",CHAR(1),LEN(A5)-LEN(SUBSTITUTE(A5,"/",""))-1))-1),LEFT(A5,FIND("/",A5)-1))'>text/textexttext</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A5)-LEN(SUBSTITUTE(A5,"/","")))>1,LEFT(SUBSTITUTE(A5,B5&"/",""),FIND("/",SUBSTITUTE(A5,B5&"/",""))-1),SUBSTITUTE(A5,B5&"/",""))'>s2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A5)-LEN(SUBSTITUTE(A5,"/","")))>1,SUBSTITUTE(A5,B5&"/"&C5&"/",""),"")'>c2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:str="text/stext/ctext/s2/c2 ">text/stext/ctext/s2/c2 </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A6)-LEN(SUBSTITUTE(A6,"/","")))>1,LEFT(A6,FIND(CHAR(1),SUBSTITUTE(A6,"/",CHAR(1),LEN(A6)-LEN(SUBSTITUTE(A6,"/",""))-1))-1),LEFT(A6,FIND("/",A6)-1))'>text/stext/ctext</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:fmla='=IF((LEN(A6)-LEN(SUBSTITUTE(A6,"/","")))>1,LEFT(SUBSTITUTE(A6,B6&"/",""),FIND("/",SUBSTITUTE(A6,B6&"/",""))-1),SUBSTITUTE(A6,B6&"/",""))'>s2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 x:str="c2 " x:fmla='=IF((LEN(A6)-LEN(SUBSTITUTE(A6,"/","")))>1,SUBSTITUTE(A6,B6&"/"&C6&"/",""),"")'>c2 </TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 365pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=487 x:str><COLGROUP><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" width=159><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 150pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=200>PES 2011/SE 2011/S1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 119pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=159 x:fmla='=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-1),LEFT(A1,FIND("/",A1)-1))'>PES 2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 x:fmla='=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,LEFT(SUBSTITUTE(A1,B1&"/",""),FIND("/",SUBSTITUTE(A1,B1&"/",""))-1),SUBSTITUTE(A1,B1&"/",""))'>SE 2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=64 x:fmla='=IF((LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))>1,SUBSTITUTE(A1,B1&"/"&C1&"/",""),"")'>S1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>Split/Second/S1/C2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))>1,LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))-1))-1),LEFT(A2,FIND("/",A2)-1))'>Split/Second</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))>1,LEFT(SUBSTITUTE(A2,B2&"/",""),FIND("/",SUBSTITUTE(A2,B2&"/",""))-1),SUBSTITUTE(A2,B2&"/",""))'>S1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF((LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))>1,SUBSTITUTE(A2,B2&"/"&C2&"/",""),"")'>C2</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>AC/DC LIVE*: Rock Band/S1/C3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF((LEN(A3)-LEN(SUBSTITUTE(A3,"/","")))>1,LEFT(A3,FIND(CHAR(1),SUBSTITUTE(A3,"/",CHAR(1),LEN(A3)-LEN(SUBSTITUTE(A3,"/",""))-1))-1),LEFT(A3,FIND("/",A3)-1))'>AC/DC LIVE*: Rock Band</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF((LEN(A3)-LEN(SUBSTITUTE(A3,"/","")))>1,LEFT(SUBSTITUTE(A3,B3&"/",""),FIND("/",SUBSTITUTE(A3,B3&"/",""))-1),SUBSTITUTE(A3,B3&"/",""))'>S1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:fmla='=IF((LEN(A3)-LEN(SUBSTITUTE(A3,"/","")))>1,SUBSTITUTE(A3,B3&"/"&C3&"/",""),"")'>C3</TD></TR></TBODY></TABLE>

Isn't that what you want?

 

[hanmas]

Hi Andrew,
It's almost right but not quite (and is in fact much closer than the TextToColumns method I was using as it correctly deals with most additional "/" characters). The remaining issue is with the PES 2011 example below where there is an additional "/s" but no "/c":

Split/Second/S1/C2 Split/Second S1 C2 - this is now correct
PES 2011/SE 2011/S1 PES 2011 SE 2011 S1 - this is still incorrect

How can we deal with the "missing" /C ?
Thanks again for your speedy response!
Graham

 

[MickG]

VBA Option:-
NB:- This will change the data in Column "A"

[COLOR=navy]Sub[/COLOR] MG19Feb40
[COLOR=navy]Dim[/COLOR] Rng     [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn      [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Sp
[COLOR=navy]Dim[/COLOR] n       [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Dim[/COLOR] Txt     [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Temp    [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] c       [COLOR=navy]As[/COLOR] [COLOR=navy]Integer[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        Temp = Dn
        Sp = Split(Temp, "/")
        [COLOR=navy]For[/COLOR] n = 0 To UBound(Sp)
            [COLOR=navy]If[/COLOR] LCase(Left(Sp(n), 1)) = "c" And IsNumeric(Mid(Sp(n), 2, 1)) Or LCase(Left(Sp(n), 1)) = "s" And IsNumeric(Mid(Sp(n), 2, 1)) [COLOR=navy]Then[/COLOR]
                 c = c + 1
                    Dn.Offset(, c) = Sp(n)
            [COLOR=navy]Else[/COLOR]
                Txt = Txt & "/" & Sp(n)
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] n
            Dn = Mid(Txt, 2)
            c = 0
            Txt = ""
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[hanmas]

Brilliant, works perfectly. Thank you so much

 

[hanmas]

Hello again,
I'm running this against 30,000 rows and it is taking a *very* long time to run. Could anyone please help suggest a faster method
Thanks,
Graham

 

[Andrew Poulsom]

Try these formulas (although I don't know if they will be any quicker):

=IF(LEFT(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255),1)="c",LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))-1))-1),LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1))

=IF(LEFT(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255),1)="c",LEFT(SUBSTITUTE(A1,B1&"/",""),FIND("/",SUBSTITUTE(A1,B1&"/",""))-1),SUBSTITUTE(A1,B1&"/",""))

=IF(LEFT(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255),1)="c",SUBSTITUTE(SUBSTITUTE(A1,B1&"/",""),C1&"/",""),"")

 

[hanmas]

Thanks Andrew