• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding the position of the nth character in a cell

D

DashboardNovice

Member
In the attached file, I have an Item list in column B. There is a hierarchy. Each level is separated by a ":"

I want to extract the lowest (last) level. Column C shows what the results should be.

To do this, I need to find the position of the nth ":" in a cell. If I can pinpoint that location, I should able to use the FIND function to extract the remaining bit.

Is there a function which will allow you to find the position number of the nth occurrence of a character?
 

Attachments

  • Chandoo - Item List Question.xlsx
    8.1 KB · Views: 18
Try this formula:

=IFERROR(REPLACE(B4,1,SEARCH("||",SUBSTITUTE(B4,":","||",LEN(B4)-LEN(SUBSTITUTE(B4,":","")))),""),B4)
 
Hi Colin and Smallman. I'd be interested to know how you would generalize those formulas so that they could work on any element and delimiter.

Smallman: I had a crack at yours, and came up with this:
Code: 
=TRIM(MID(SUBSTITUTE(String,Delimiter,REPT(" ",255)),(LEN(String)-LEN(SUBSTITUTE(String,Delimiter,""))+Element-3)*255+1,255))

...although I'm not entirely sure I've got the start position right.


Here's a generalised formula I put together some time back to do the same thing:
Code: 
=MID(String,FIND("|",SUBSTITUTE(Delimiter&String&Delimiter,Delimiter,"|",Element)),SUM(FIND("|",SUBSTITUTE(Delimiter&String&Delimiter,Delimiter,"|",Element+{0,1}))*{-1,1})-1)
 
So I think the generalised version of my formula would be:

=IFERROR(REPLACE( [STRING] ,1,SEARCH("||",SUBSTITUTE( [STRING] , [CHAR] ,"||", [N] )),""), [STRING] )

Where
[STRING] is the whole string being evaluated
[CHAR] is the character you want to find
[N] is the nth occurrence you of the character

If the character doesn't exist or the nth occurrence of the character doesn't exist then it just returns [STRING]. Obviously it doesn't work properly if [STRING] contains double pipes.
 
@Colin Legg: I see now that your formula works by replacing everything to the left of the desired element with a blank string - clever. So to generalise it we'd need to add another part that replaces everything to the right of the desired element with a blank, leaving just the element of interest.

By 'Generalise' I was meaning giving it the ability to extract a certain element. I see that your formula has a slightly different interpretation: return everything past a certain delimiter: also very handy.

I'm going to put together an ExtractElement UDF that will let you pull either the element desired, or like your formula pull everything before or after a certain delimiter.

Cheers for the inspiration.
 
Last edited:
@shrivallabha: The problem with most ExtractElement formulas and UDFs - including the formulas and UDFs referenced above - is that they don't offer the user much firepower for trickier problems. For instance, what if you have several different delimiters that you need to use to pinpoint the desired element? Or what if you want to return everything before a delimiter? Or everything after?

You need to have a different formula up your sleeve to handle these, which is a pain. And I haven't come across a great UDF that does it all.

I'm going to amend Chip Pearson's great SplitEx function at http://www.cpearson.com/excel/splitondelimiters.aspx so it can be called from the worksheet as an array funciton, and can do the 'everything before'/'everything after' return too if a user wants.

Chip's funciton allows mutiple delimiters of different lenghts. For example, it allows you to split the following:
"this|is some::delimited,text"
...using different delimiters such as these:
  • |
  • ::
  • ,
so that you get this:
This
is some
delimited
text

I'm going to add some more bells and whistles too...for instance an option where you can enforce the order of those delimiters.

Here's Chip's function as is, if you're interested. It's not set up to be used as a UDF yet, mind:

Code: 
Function SplitEx(ByVal InString As String, IgnoreDoubleDelmiters As Boolean, _
ParamArray Delims() As Variant) As String()
Dim Arr() As String
Dim Ndx As Long
Dim N As Long

If Len(InString) = 0 Then
SplitEx = Arr
Exit Function
End If
If IgnoreDoubleDelmiters = True Then
For Ndx = LBound(Delims) To UBound(Delims)
N = InStr(1, InString, Delims(Ndx) & Delims(Ndx), vbTextCompare)
Do Until N = 0
InString = Replace(InString, Delims(Ndx) & Delims(Ndx), Delims(Ndx))
N = InStr(1, InString, Delims(Ndx) & Delims(Ndx), vbTextCompare)
Loop
Next Ndx
End If


ReDim Arr(1 To Len(InString))
For Ndx = LBound(Delims) To UBound(Delims)
InString = Replace(InString, Delims(Ndx), Chr(1))
Next Ndx
Arr = Split(InString, Chr(1))
SplitEx = Arr
End Function
This allows mutiple delimiters of different lenghts. For example, to split on |, :: and , you would call the function like so:
Code: 
Sub AAA()
Dim S As String
Dim T() As String
Dim N As Long
S = "this|is some::delimited,text"
T = SplitEx(S, True, "|", "::", ",")
For N = LBound(T) To UBound(T)
Debug.Print N, T(N)
Next N
End Sub
 
You must log in or register to reply here.
Back
Top