CODEWITHSUNDEEP
excelvba
Grzegorz
Grzegorz

Reputation: 5

Copy values between two characters VBA

I have a problem with copy value between same characters. Example below.

Let's say we have text like this:

"asd|qweeee|1123444|45555512345|swdq|" or 
"asdddd|qwwweeee|1123444|45555512345990|swdq|"

Is there any option to get value "1123444" from this string?

I want to copy values after 4 occurring character like "|".

in another words:

Copy value between 2nd "|" and 3rd "|".

Length of string is not the same.

I will be really thankful for any tips and help.

Upvotes: 0

Views: 106

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

It appears as if you don't want to include empty segments when determining which to return.

That being the case, you can use a Regular Expression also:

Option Explicit
Function extrSegment(S As String, Optional Segment As Long = 3) As String
    Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = "([^|]+)"
    .Global = True
    If .Test(S) = True Then
        extrSegment = .Execute(S)(Segment - 1).submatches(0)
    End If
End With
End Function

Note that there is an optional argument in the function for which segment to return, where 1 is the first segment.

You can also do this with a worksheet formula, especially if you have Excel 2013+ with the FILTERXML function:

=FILTERXML("<t><s>" & SUBSTITUTE(A1,"|","</s><s>")& "</s></t>","//s[text()!=''][3]")

That formula, also, will exclude empty segments from the determination of which is #3. However, if the segment has leading zero's, they will be dropped by the FILTERXML function.

Both methods result in:

enter image description here

Edit:

If you want to include empty nodes/segments in your count, then you can remove the text test from the xPath in the formula.

If you do that, FILTERXML will return an error for an empty node, so we need to test for that:

=IFERROR(FILTERXML("<t><s>" & SUBSTITUTE(E1,"|","</s><s>")& "</s></t>","//s[3]"),"")

and the UDF can be simplified to just:

Function extrSegment(S As String, Optional Segment As Long = 4, Optional Delimiter As String = "|") As String
    extrSegment = Split(S, Delimiter)(Segment-1) 'array will be 0-based
End Function

Upvotes: 0

FaneDuru
FaneDuru

Reputation: 42236

For your last request use the next code:

Sub testStringArr()
  Dim x As String
   x = "asd|||qweeee||1123444|45555512345|swdq|"
   Debug.Print Split(Replace(Replace(Trim(x), "|||", "|"), "||", "|"), "|")(2)
End Sub

But, if your string containing "| | |" comes from a function which could not find some elements and introduced spaces " " separated by "|", it is better to know where from this string is extracted and use it like it is, without the sausage formula. Just split it by "|" and return the appropriate number of array element according to your investigation about the string origine.

Upvotes: 0

JvdV
JvdV

Reputation: 75860

Debug.Print Split(<your_string>, "|")(2)

In your example this would return "1123444". Note that Split will return an array with starting index 0.

  • 1st Element = Split(<your string>, "|")(0)
  • 2nd Element = Split(<your string>, "|")(1)
  • Etc..

If you got empty elements in the returned array, either loop them or make sure you "filter" them out beforehand, for example:

Debug.Print Split(Application.Trim(Replace(<your_string>, "|", " ")), " ")(2)

Upvotes: 2

shahkalpesh
shahkalpesh

Reputation: 33474

Dim s As String, items
s = "asd|qweeee|1123444|45555512345|swdq|"

items = Split(s, "|")
MsgBox items(2)

Upvotes: 1

Related Questions