Extract text in middle using Mid and Find or Search
 

Hi,

Here's the string I need to extract from: Regional Sales: SMITH, E.
(330146)
Here's what I want to end up with: SMITH
All the rows contain the same beginning (Regional Sales: ) (:space), it's
the length of the last name that varies, but all of the last names end with a
comma. I've been spinning my wheels, I know I'm close, but I just can't get
it. TIA :)

Extract text in middle using Mid and Find or Search
 

On Wed, 23 Jan 2008 06:38:09 -0800, Karin
wrote:

Hi,

Here's the string I need to extract from: Regional Sales: SMITH, E.
(330146)
Here's what I want to end up with: SMITH
All the rows contain the same beginning (Regional Sales: ) (:space), it's
the length of the last name that varies, but all of the last names end with a
comma. I've been spinning my wheels, I know I'm close, but I just can't get
it. TIA :)

Your precise description is commendable and makes formulating a solution
simple. Thank you for that.

=MID(A1,FIND(":",A1)+2,FIND(",",A1)-FIND(":",A1)-2)
--ron

Extract text in middle using Mid and Find or Search
 

=MID(A1,SEARCH(": ",A1)+2,SEARCH(",",A1)-SEARCH(": ",A1)-2)

Regards,
Stefi


€žKarin€� ezt Ã*rta:

Hi,

Here's the string I need to extract from: Regional Sales: SMITH, E.
(330146)
Here's what I want to end up with: SMITH
All the rows contain the same beginning (Regional Sales: ) (:space), it's
the length of the last name that varies, but all of the last names end with a
comma. I've been spinning my wheels, I know I'm close, but I just can't get
it. TIA :)

Extract text in middle using Mid and Find or Search
 

Try this:

=MID(A1,17,FIND(",",A1)-17)


"Karin" wrote:

Hi,

Here's the string I need to extract from: Regional Sales: SMITH, E.
(330146)
Here's what I want to end up with: SMITH
All the rows contain the same beginning (Regional Sales: ) (:space), it's
the length of the last name that varies, but all of the last names end with a
comma. I've been spinning my wheels, I know I'm close, but I just can't get
it. TIA :)

Extract text in middle using Mid and Find or Search
 

=MID(A2,17,FIND(",",A2)-17)
--
David Biddulph

"Karin" wrote in message
...
Hi,

Here's the string I need to extract from: Regional Sales: SMITH, E.
(330146)
Here's what I want to end up with: SMITH
All the rows contain the same beginning (Regional Sales: ) (:space), it's
the length of the last name that varies, but all of the last names end
with a
comma. I've been spinning my wheels, I know I'm close, but I just can't
get
it. TIA :)

Extract text in middle using Mid and Find or Search
 

I envy you for recognizing the simpler solution!
Stefi


€žTeethless mama€� ezt Ã*rta:

Try this:

=MID(A1,17,FIND(",",A1)-17)


"Karin" wrote:

Hi,

Here's the string I need to extract from: Regional Sales: SMITH, E.
(330146)
Here's what I want to end up with: SMITH
All the rows contain the same beginning (Regional Sales: ) (:space), it's
the length of the last name that varies, but all of the last names end with a
comma. I've been spinning my wheels, I know I'm close, but I just can't get
it. TIA :)