Extract text from string

[saltkev]

VBA Good Morning Guys I hope you can help. I need to extract specific pieces of text from a string. The string will differ in length but will keep the same format between "/" The pieces of text are identified in RED. There will thousands of lines so I will run it through a For Loop 2 ;V5745463100800/UBN1/N/1/0/10/50313213/1 Many Thanks Kev

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you tell us what it is that identifies exactly what is is that has to be extracted?

 

[saltkev]

Hi

The First piece of text is always preceeded by a semi-colon ";" and ends prior to the first obleque "/"
The Second piece of text always starts imediatly after the 5th Oblique "/" and ends prior to the 6th Oblique "/"

Kev

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Can you tell us what it is that identifies exactly what is is that has to be extracted?

Hi

The First piece of text is always preceeded by a semi-colon ";" and ends prior to the first obleque "/"
The Second piece of text always starts imediatly after the 5th Oblique "/" and ends prior to the 6th Oblique "/"

Kev

 

[Peter_SSs]

Thanks for the extra information, but what about the first thing I suggested?

suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

In the meantime, see if this helps. It assumes the data is in column A
Test with a copy of your workbook.

Sub ExtractText()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(#="""","""",replace(left(#,find(""/"",#&""/"")-1),1,find("";"",#),""""))", "#", .Address))
    .Offset(, 2).Value = Evaluate(Replace("trim(mid(substitute(#,""/"",REPT("" "",100)),500,100))", "#", .Address))
  End With
End Sub

 

[saltkev]

Thanks for the extra information, but what about the first thing I suggested?

Not sure what more you want?

 

[Peter_SSs]

Not sure what more you want?

I wanted to know your Excel version. I see that you have updated it now but it was not showing for me at the time I asked.
Anyway, let's see what the result of my code suggestion is.

 

[saltkev]

In the meantime, see if this helps. It assumes the data is in column A
Test with a copy of your workbook.

Sub ExtractText()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).Value = Evaluate(Replace("if(#="""","""",replace(left(#,find(""/"",#&""/"")-1),1,find("";"",#),""""))", "#", .Address))
    .Offset(, 2).Value = Evaluate(Replace("trim(mid(substitute(#,""/"",REPT("" "",100)),500,100))", "#", .Address))
  End With
End Sub

Not sure what more you want?

I can now see your reply,

The first line is working well the second not working, P.S the data is in "B", I changed your ranges, or I can copy to Col "A"

Thanks fort your help

 

[saltkev]

I can now see your reply,

The first line is working well the second not working, P.S the data is in "B", I changed your ranges, or I can copy to Col "A"

Thanks fort your help

Results

 

[saltkev]

OK Range mistake on my part now working a little better but is taking second piece of text from "B2" and repeating