How to extract a varying character-length piece of text from the center of cell's text

**RuthK** · 08-13-2014, 10:15 PM

Your help would be sincerely appreciated!

I am hoping you would know of a formula to extract text from the center of a cell's text contents. (I don't care whether commas are returned in the result or not, although I will later use the formula result in calculations.)

In the attached example I've colored red the text I would want to result in the formula's answer. The good news is that the desired text always follows after "Total Revenue: ". The data to be extracted can vary in length, sometimes contains a comma within it as part of the number, and is then always followed by a comma and space.

I've tried using the FIND/MID formulas, but just don't have the technical expertise to handle anything as complicated as what I'm trying to achieve.

Looking forward to an encouraging answer to end my day on a high ...

**millz** · 08-13-2014, 10:27 PM

Hi RuthK, try this formula:

Please Login or Register  to view this content.

**azumi** · 08-13-2014, 10:33 PM

In A2

=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("Total Revenue:",A2,1),LEN(A2)),"Total Revenue:",""),SEARCH(", ",SUBSTITUTE(MID(A2,SEARCH("Total Revenue:",A2,1),LEN(A2)),"Total Revenue:",""),1)-1))+0

and copied down as necessary

**RuthK** · 08-13-2014, 10:49 PM

Wow!!! Both of those options work beautifully, so to both of you, a HUGE Thank You! I'm adding you to my Hero List.

**azumi** · 08-13-2014, 11:03 PM

You're welcome, and thanks for provide feedback....

How to extract a varying character-length piece of text from the center of cell's text

LinkBack

Thread Tools

Rate This Thread

Display

How to extract a varying character-length piece of text from the center of cell's text

Re: How to extract a varying character-length piece of text from the center of cell's text

Re: How to extract a varying character-length piece of text from the center of cell's text

Re: How to extract a varying character-length piece of text from the center of cell's text

Re: How to extract a varying character-length piece of text from the center of cell's text

Thread Information

Users Browsing this Thread

Similar Threads

Removing various different text, from varying length text strings

Text to Column with Text & Varying-length Numbers

Extract variable length of text from cell

[SOLVED] want to remove all text characters equal to one character in length from text string

want to remove all text characters equal to one character in length from text string

Bookmarks

Bookmarks

Posting Permissions