How to get string in between two characters in excel/spreadsheet

Question

I have this string

Weiss,Emery/Ap #519-8997 Quam. Street/Hawaiian Gardens,IN - 79589|10/13/2010

how do I get the Hawaiian Gardens only?

I already tried Using some

=mid(left(A1,find("/",A1)-1),find(",",A1)+1,len(A1))

it gives me emery instead

This might be very hard to handle using pure Excel. If you have long term needs similar to this, it might be worthwhile to spend some time learning about VBA for Excel. From VBA code, you would have a number of options for handling this problem. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 12, 2019 at 5:24

OverflowStacker · Accepted Answer · 2019-06-12 07:25:31Z

2

If there are always two slashes before the string you want to extract, based onTyler M's answer you can use this

=MID(E1,
     FIND("~",SUBSTITUTE(E1,"/","~",2))+1,
     FIND(",",RIGHT(E1,LEN(E1)-FIND("~",SUBSTITUTE(E1,"/","~",2))))-1
     )

This substitutes the second occurence of / with a character which normally would not occur in the address, thus making it findable.

edited Jun 12, 2019 at 7:25

answered Jun 12, 2019 at 6:31

OverflowStacker

1,3381 gold badge11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JvdV · Accepted Answer · 2019-06-12 07:45:34Z

Was your intention to also include Google Spreadsheets (looking at your title)? If so,you can use the REGEXEXTRACT() function. For example in B1

=REGEXEXTRACT(A1,"\/([\w\s]*)\,")

In Excel you could build a UDF using this regex rule like so (as an example):

Function REGEXEXTRACT(S As String, PTRN As String) As String

'We will get the last possible match in your string...
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
With regex
    .Pattern = PTRN
    .Global = True
End With

Set matches = regex.Execute(S)
For Each Match In matches
    If Match.SubMatches.Count > 0 Then
        For Each subMatch In Match.SubMatches
            REGEXEXTRACT = subMatch
        Next subMatch
    End If
Next Match

End Function

Call the function in B1 like so:

=REGEXEXTRACT(A1,"\/([\w\s]*)\,")

Collectives™ on Stack Overflow

How to get string in between two characters in excel/spreadsheet

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related