Custom Function to Delimit Cell by ";" Need more delimiters

jonesalexr

New Member
Joined
Jan 29, 2014
Messages
10
Hello,

I was able to find the below code for a custom function to read a cell and pull back the delimited value to a cell.

Code:
Function ExtractElement(str, n, sepChar)
'   Returns the nth element from a string,
'   using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If n > 0 And n - 1 <= UBound(x) Then
       ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function
I am using the below formula to split the data of B2 into B3,B4,B5 etc
Cell B3=TRIM(ExtractElement($B$2, 1, ";"))
Cell B4=TRIM(ExtractElement($B$2, 2, ";"))

Right now it only works if i use ";" as my delimiter. I would like to use anything but a number or letter as a delimiter. This could include spaces and symbols. The field that is delimited is filled out by various people and will typically use a "," or ";", but i need to include all delimiters.
 

jonesalexr

New Member
Joined
Jan 29, 2014
Messages
10
Does anyone know how to edit this function to add multiple symbols as a delimiter?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,261
Office Version
365
Platform
Windows
Is this what you mean?
Rich (BB code):
Function ExtrElement(str As String, n As Long) As String
  Static RX As Object
  
  Dim s As String
  Dim ary As Variant
  
  Const mYDelimiters As String = "., ;:'\|/!" '<- Add/remove delimiters here
    
  If RX Is Nothing Then
      Set RX = CreateObject("VBScript.RegExp")
      RX.Global = True
      RX.Pattern = "[" & mYDelimiters & "]"
  End If
  ary = Split(RX.Replace(str, "|"), "|")
  If n > 0 And n - 1 <= UBound(ary) Then
    ExtrElement = ary(n - 1)
  End If
End Function
Formula in B5 is copied across and down.

Excel Workbook
ABCDEF
5abc,def,ghidefghi
6abc.def:huiabcdefhui
7b/d/rbdr
8
9rty.d/345|asdrtyd345asd
10vbavba
Sheet2
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,261
Office Version
365
Platform
Windows
I would like to use anything but a number or letter as a delimiter.
In that case, rather than make a list of delimiters to use as in my previous code, you could try this variation.
Rich (BB code):
Function ExtrElement(str As String, n As Long) As String
  Static RX As Object
  
  Dim ary As Variant
    
  If RX Is Nothing Then
      Set RX = CreateObject("VBScript.RegExp")
      RX.Global = True
      RX.Pattern = "[^a-zA-Z0-9]"
  End If
  ary = Split(RX.Replace(str, "|"), "|")
  If n > 0 And n - 1 <= UBound(ary) Then
    ExtrElement = ary(n - 1)
  End If
End Function

Also note that the "Dim s as String" that I had in my last code should have been removed.
 
Last edited:

jonesalexr

New Member
Joined
Jan 29, 2014
Messages
10
I used the last code you posted and it worked like a charm! Thank you! I just had to change the Absolute and Rows. Is there a way to treat consecutive delimiters (of different types) as one. The problem is that if they do semi "; " to separate the data, it places a space between my rows
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
I used the last code you posted and it worked like a charm! Thank you! I just had to change the Absolute and Rows. Is there a way to treat consecutive delimiters (of different types) as one. The problem is that if they do semi "; " to separate the data, it places a space between my rows
The only possible problems with doing that is you lose your count position if there is one or more leading delimiters and valid consecutive delimiters (for example, one,,three) become meaningless. Here is a function that that does not call out to a RegExp engine the way Peter's does (the function uses native VBA functions and statements) which does what Peter's function does plus I added an optional third argument to allow you to specify whether the function should treat consecutive delimiters as one or not... I have defaulted that optional argument to True meaning if you omit the third argument (or specify True for it), consecutive non-letter, non-number characters will be treated as single delimiters whereas if you specify False for that third argument, all non-letter, non-number characters will be treated as independent delimiters. Also note that I called my function GetElement to differentiate it from Peter's.
Code:
Function GetElement(ByVal S As String, N As Long, Optional NoConsecutives As Boolean = True) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9A-Za-z]" Then Mid(S, X) = " "
  Next
  If NoConsecutives Then S = Application.Trim(S)
  If N > 0 Then GetElement = Split(S & String(N, " "))(N - 1)
End Function
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,508
Office Version
2010
Platform
Windows
Code:
Function GetElement(ByVal S As String, N As Long, Optional NoConsecutives As Boolean = True) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "[!0-9A-Za-z]" Then Mid(S, X) = " "
  Next
  If NoConsecutives Then S = Application.Trim(S)
  If N > 0 Then GetElement = Split(S & String(N, " "))(N - 1)
End Function
Just to follow up on the above code (which I posted in Message #6), here are some examples to clarify the use of that third, optional argument (note, ignore all quote marks as they are there solely to delineate the text in the cells, they will not be part of the cell's value)...

Code:
A1: "One,,Two,Three"


B1:  =GetElement(A1,2)        ==>   "Two" 


B1:  =GetElement(A1,2,TRUE)   ==>   "Two"


B1:  =GetElement(A1,2,FALSE)  ==>   ""
 
Last edited:

jonesalexr

New Member
Joined
Jan 29, 2014
Messages
10
Just to follow up on the above code (which I posted in Message #6), here are some examples to clarify the use of that third, optional argument (note, ignore all quote marks as they are there solely to delineate the text in the cells, they will not be part of the cell's value)...

Code:
A1: "One,,Two,Three"


B1:  =GetElement(A1,2)        ==>   "Two" 


B1:  =GetElement(A1,2,TRUE)   ==>   "Two"


B1:  =GetElement(A1,2,FALSE)  ==>   ""
You are awesome! Thats great. I really dont care if it skips a blank delimited column, I just want to make sure I don't have to double the length of my lookup table to account for spaced cells between the data. Have a great weekend!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,261
Office Version
365
Platform
Windows
Is there a way to treat consecutive delimiters (of different types) as one.
For my function, just adding a "+" sign as shown will treat consecutive delimiters, whether they are the same or different, as one.
Rich (BB code):
RX.Pattern = "[^a-zA-Z0-9]+"
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top