Copy values between two characters VBA

Related searches

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 

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.

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:


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

Easily Extract Text Between Two Strings with VBA, =SuperMid(A9,"abc","xyz") into a cell in Excel. Extract the string between two words. Extract the characters between two delimiters. Extract between the last occurrence of two substrings. Extract all the text after a keyword. Extract a file name from path with VBA. Easily extract text between two strings with this VBA Function. This user-defined function (UDF) can extract a substring between two characters, delimiters, words and more. The delimiters can be the same delimiter or unique strings. I call this UDF SuperMid because it behaves like the VBA Mid Function on steroids. First, I’ll show you the function.

Extract text string between two characters using Excel and VBA , To extract a text string between two characters you can use a combination of Excel MID, This tutorial provides both Excel and VBA methods. In this example cell ("C5") and ("C6") capture the values between which to extract a text string. excel vba- extract text between 2 characters. firstDelPos – 1) ‘ extract the string between two delimiters MsgBox (stringBwDels) ‘ message shows string

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

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

Extract string from between 2 delimiters in a string (VBA), Hi I've a function 'piece' that extracts text from between 2 delimiters in a string. there was a function in XL2007 VBA that did it without the character loop. Also, tell us in what cells your data is in and what cells you want the� From and To text: Select the from and to value by either changing the values in cells ("C5") and ("C6") or changing the from and to cell references in the VBA code. Explanation about the formulas used to extract text string between two characters

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.

How to Extract Text between Two Text Strings in Excel, Assuming that you have a list of data in range B1:B5, in which contain text string values. And you need to extract all text strings between two� In your example of 123-abc-567, what if you had specified 1, would you want to return 123? Technically that doesn't meet your criteria of being between the two delimiters. The first and only case that that happens is abc which would then be 1. If what you really want is the section separated by the specified delimiter, then this should work.

How to return everything between two characters in Excel?, You could use Excel's Text to Columns functionality under the Data menu. It will take several passes but you can do it without formula or vba. Using The Copy/PasteSpecial Method This is the VBA code version of how you would manually paste values only in Excel with your mouse and keyboard. The code is simply copying a range and pasting the values only in a destination range.

In VBA, To paste values only, there are various other options available apart from range. Recommended Articles. This is a guide to VBA Paste Values. Here we discuss how to use Excel VBA Paste Values function along with practical examples and downloadable excel template. You can also go through our other suggested articles – Copy Paste

I am trying to write VBA for the below. If the value in column AA is =”-” and the value in column AC is $0.00 then copy the value in column X to Column AA. My data set is ever changing so I can not set specific ranges and it may be the case where I don’t have data the satisfies the above criteria.

  • What do you mean by "ignore ||? What about |||? What would you expect for output from line 3 above?
  • For example we have text like this: "ED1|22222|123123123|FFFF|DDDD" and "ED1|22222||FFFF|DDDD" And I want to give me "FFFF" from both, any tips? I am using your worksheet formula, it is better for me.
  • @Grzegorz See my Edit
  • I checked and now looks really great. Thank You Ron! Really appreciate it :)
  • What if string is like this? "asd|||qweeee||1123444|45555512345|swdq|" There is blank values between "|". Macro ignoring blank values, I don't want to. Any tips?
  • It's working for me. Perfect! That is what I was looking for. Thank you so much! :)
  • What if string is like this? "asd|||qweeee||1123444|45555512345|swdq|" There is blank values between "|". Macro ignoring blank values, I don't want to. Any tips?
  • @Grzegorz: If you are not able to set a rule able to be understood by human people and your string will not look the same, only God can do that, not Excel VBA... For your last issue, try this: Dim x As String x = "asd|||qweeee||1123444|45555512345|swdq|" Debug.Print Split(Replace(Replace(Trim(x), "|||", "|"), "||", "|"), "|")(2). I do not know if you can understand the code from here. I will try an answer...