Isolate string of next

**fillgolo** · 06-18-2013, 07:18 PM

I need to isolate a string of text that at times may vary in length. The string in question is: IN.Tags.Share.handleCount({"count":2,"fCnt":"2","fCntPlusOne":"3","url":"http:\/\/relevance.com\/blog\/blog-editing-tip-use-adverbs-carfeully\/"});

I'm interested in isolating the number highlighted in red; however, the function (or combo of functions) must be able to gather numbers that may be multiple digits in length.

Super thanks in advance,

Phil

**Tony Valko** · 06-18-2013, 07:31 PM

Looks like you want to extract the 1st number in the string.

Assuming that number will not have leading 0s (02):

=LOOKUP(1E100,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2)))))

**Tony Valko** · 06-18-2013, 07:49 PM

We might be able to use a less complicated formula if the characteristics of the number follow some constant rules.

For example...

The number will never be more than 6 digits long.
The number will not have leading 0s.
The number is always located after the 1st colon.

=LOOKUP(1E100,--MID(A2,FIND(":",A2)+1,{1,2,3,4,5,6}))

**Pete_UK** · 06-18-2013, 09:12 PM

Yes, I was wondering if you could look for the colon and the comma - how common are these in your data? Can you post a few more examples, showing the range of text strings that you might encounter?

Pete

**fillgolo** · 06-19-2013, 09:53 AM

Here are some more to parse for fun:

IN.Tags.Share.handleCount({"count":232,"fCnt":"2","fCntPlusOne":"3","url":"http:\/\/relevance.com\/blog\/blog-editing-tip-use-adverbs-carfeully\/"});

IN.Tags.Share.handleCount({"count":47,"fCnt":"1","fCntPlusOne":"2","url":"http:\/\/blog.avectra.com\/spend-a-day-in-the-life-of-a-member\/"});

In short, it's basically what the LinkedIn API spits out; therefore, the general form should be the same for each call.

You all are great. Thank you again,

Phil