How to select one item in a cell value separated by characters?

I have a list of cities in this format in each cell:
Baltimore|Maryland|United States
Frederick|Maryland|United States
Chicago|Illinois|United States

I need a custom function to pull out the 2nd one in each cell.
Maryland
Maryland
Illinois
Parsing.xlsx
LVL 1
Alex CampbellAsked:
Who is Participating?
 
Ryan ChongCommented:
try use a custom function like this:

Public Function SplitString(value As String, location As Integer, Optional delimiter As String = "|") As String
    Dim tmpArr() As String
    
    On Error GoTo EH
    tmpArr = Split(value, delimiter, , vbTextCompare)
    SplitString = tmpArr(location - 1)
    
    Exit Function
EH:
    SplitString = ""
End Function

Open in new window


then in your worksheet, put the formula like:

=SplitString(A1,2)

Open in new window

Parsing_b.xlsm
0
 
Ejgil HedegaardCommented:
Use this formula
=MID(A1,SEARCH("|",A1)+1,SEARCH("|",A1,SEARCH("|",A1)+1)-1-SEARCH("|",A1))

Or mark the items in column A and use the "Text to columns" function on the Data tab, and use | as delimiter.
Parsing.xlsx
0
 
Alex CampbellAuthor Commented:
Thank you for the suggestion of the formula.
I would like to see how it would work as a custom function.
That is because I am working on learning how to build custom functions.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Alex CampbellAuthor Commented:
wonderful. Just what I was looking for
0
 
Alex CampbellAuthor Commented:
I don't see how to ask a related question any more.
How could the function be adapted to pass the character to be used as the separator?
0
 
Ryan ChongCommented:
How could the function be adapted to pass the character to be used as the separator?

try change the 3rd parameter in that function, like:

=SplitString(A1,2, "*")

which means * is the separator
0
 
Alex CampbellAuthor Commented:
Great, thanks!
0
 
Alex CampbellAuthor Commented:
Great, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.