Extract portion of cell contents

R

Rick

Have a text string that could look something like this:
http://he4.clientthirddheytrs.com/wyt/cftts/edit.aspx?id={840102F6-AJUE-SD1B-9705-00188B2E03D9}

I need to extract the ID number which is surrounded by "the brackets".
Desired result in this instance would be:
{840102F6-AJUE-SD1B-9705-00188B2}

There is no consistency in the data; there can be any number of
characters between the brackets, and any number of characters before,
or after the brackets. It's all formatted as text.

Ideas?
Thanks in advance.
Rick
 
R

Rick Rothstein

Assuming the ID always comes at the end of the text (as you example
shows)...

=MID(A1,FIND("=",A1)+1,99)
 
R

Rick

Assuming the ID always comes at the end of the text (as you example
shows)...

=MID(A1,FIND("=",A1)+1,99)

--
Rick (MVP - Excel)









- Show quoted text -

Rick, Oops. There will be instances where characters will appear
after the last bracket. I appologize for the poor example.
Those need to be stripped off as well. Want to take another run at it?
Thanks again for your assistance.
Rick
 
R

Rick

Rick: found one that works after poking around the archives for
similar formulas,a dn utilizing your formula and inspiration. . .

=LEFT(MID(A1,FIND("=",A1)+1,99),FIND("#",MID(A1,FIND("=",A1)+1,99))-1)

First bracket is always preceeded by a =, and last bracked is followed
by a #.


Many many thanks.
Rick
 
L

L. Howard Kittle

Hi Rick,

Here's one more just for fun.

=MID(A4,FIND("{",A4)+1,FIND("}",A4)-FIND("{",A4)-1)

HTH
Regards,
Howard
 
R

Rik_UK

Rik

I would suggest the following amendment as an enhanced version of the formula

=LEFT(MID(A1,FIND("{",A1),LEN(A1)),FIND("}",MID(A1,FIND("{",A1),LEN(A1))))

this simplifies things slightly, and in your original post you said the
number of characters between the brackets is not fixed... so this removes the
limitation of 99 characters, as you have currently got.

Hope this is ok...

--
If this is the answer has helped please remember to click the yes button
below...

Kind regards

Rik
 
R

Rick Rothstein

Or event this approach using the two curly brackets as you suggest, but
using a simpler construction than the OP's found solution (still using MID,
but using a much larger number of character)...

=MID(LEFT(A1,FIND("}",A1)),FIND("{",A1),999)
 
R

Rick

Hi Rick,

Here's one more just for fun.

=MID(A4,FIND("{",A4)+1,FIND("}",A4)-FIND("{",A4)-1)

HTH
Regards,
Howard









- Show quoted text -

You guys are awesome!
Thank you so much for your efforts on this.
Rick
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top