CODEWITHSUNDEEP
stringexcelparsing
leora
leora

Reputation: 196639

What is the best way in Excel formula to parse out this string?

I have a column of data and each cell has something like:

   Last First (Id)

such as :

  Thompson Joe (ABC12323)

and i want to parse out the:

  ABC12323

NOTE: in some rare cases I see there are two ids listed like this:

  Thompson Joe (ABC12323) (DEF1123432)

and in this case i would want to parse out the second one

   DEF1123432

what is the easiest way to do this in an excel formula?

Upvotes: 0

Views: 89

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

Alternate solution:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(TRIM(A1),")",""),"(",REPT(" ",LEN(A1))),LEN(A1)))

Upvotes: 0

JNevill
JNevill

Reputation: 50200

You can use this nasty beast:

=RIGHT(A1,LEN(A1) - FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

The LEN(A1)-LEN(SUBSTITUTE(A1," ","") will find the number of occurrences of space " ". The Find(Substitute) will give you the length of the cell up to that last occurrence of a space. Then it just uses Right() to pick that last section out.

The cool thing is that this will return the last word in a sentence, regardless of it's length.

Updated: Added a couple substitutes to get rid of the parantheses:

=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))),"(",""), ")", "")

Upvotes: 2

Related Questions