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

 

[Fluff]

How about this tweak to Peter's code

.Offset(, 2).Value = Evaluate(Replace("if({1},trim(mid(substitute(#,""/"",REPT("" "",100)),500,100)))", "#", .Address))

 

[saltkev]

Absolutley spot on. Thank You Both

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kvsrinivasamurthy]

Try this code . Data starts from A3 and down. From B3 And C3 O/p result will be available.

Sub GetStrings()
Dim A, M, N
Dim Ros As Long, T As Long

A = Range("A3").CurrentRegion
Ros = UBound(A, 1)
ReDim B(1 To Ros, 1 To 1)
ReDim C(1 To Ros, 1 To 1)
For T = 1 To Ros
M = Split(A(T, 1), ";")
N = Split(M(1), "/")
B(T, 1) = N(0): C(T, 1) = N(5)
Next T
With Range("A3").Resize(Ros, 1)
.Offset(0, 1) = B
.Offset(0, 2) = C
End With
End Sub

 

[saltkev]

Try this code . Data starts from A3 and down. From B3 And C3 O/p result will be available.

Sub GetStrings()
Dim A, M, N
Dim Ros As Long, T As Long

A = Range("A3").CurrentRegion
Ros = UBound(A, 1)
ReDim B(1 To Ros, 1 To 1)
ReDim C(1 To Ros, 1 To 1)
For T = 1 To Ros
M = Split(A(T, 1), ";")
N = Split(M(1), "/")
B(T, 1) = N(0): C(T, 1) = N(5)
Next T
With Range("A3").Resize(Ros, 1)
.Offset(0, 1) = B
.Offset(0, 2) = C
End With
End Sub

Works a Treat Thanks