I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

try creating 5 new columns, going to text-to-columns
select Delimited

Go to other and type ;

Press ok

"Mike" wrote:

I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

I should have stated that I was trying to not use the Text To Columns.

"AKphidelt" wrote:

try creating 5 new columns, going to text-to-columns
select Delimited

Go to other and type ;

Press ok

"Mike" wrote:

I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

Assuming every entry has at least 3 ";"

I also notice there is a space after Sweep

=LEFT(MID(A1,FIND(";",A1,FIND(";",A1)+1)+1,255),FI ND(";",MID(A1,FIND(";",A1,FIND(";",A1)+1)+1,255))-2)

Biff

"Mike" wrote in message
...
I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case
the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

lol, well that is going to be a difficult task... try this

=MID(I26,1+(FIND(";",I26,(FIND(";",I26,1)+1))),(FI ND(";",I26,3+FIND(";",I26,1+(FIND(";",I26,1)))))-(FIND(";",I26,1+FIND(";",I26,1)))-1)

That's the best I can do, other then VBA i see no other way of doing this...
but then again I'm not the smartest excel person around.

"Mike" wrote:

I should have stated that I was trying to not use the Text To Columns.

"AKphidelt" wrote:

try creating 5 new columns, going to text-to-columns
select Delimited

Go to other and type ;

Press ok

"Mike" wrote:

I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

Try this:

=MID(A1,FIND(";",A1,FIND(";",A1)+1)+1,FIND(";",A1, FIND(";",A1,FIND(";",A1)+1)+1)-FIND(";",A1,FIND(";",A1)+1)-1)


"Mike" wrote:

I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

Another way...

=MID(A1,FIND("^",SUBSTITUTE(A1,";","^",2))+1,FIND( "^",SUBSTITUTE(A1,";","^",3))-FIND("^",SUBSTITUTE(A1,";","^",2))-1)


"Mike" wrote:

I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?

Both of your formulas will pickup the space after Sweep.

Need to subtract 2 at the end.

Biff

"Teethless mama" wrote in message
...
Another way...

=MID(A1,FIND("^",SUBSTITUTE(A1,";","^",2))+1,FIND( "^",SUBSTITUTE(A1,";","^",3))-FIND("^",SUBSTITUTE(A1,";","^",2))-1)


"Mike" wrote:

I have a cell that contains the following string:

America;005491;I/C FAMS Sweep ;05/01/2007;25511.30;106699

I would like a formula that would extract the string that is after the
second semi colon and that is before the third semi colon. In this case
the
formula would return:

I/C FAMS Sweep

I will be copying this formula to other rows and the problem is that the
number between the second and third semi colon's will vary. This makes
the
use of MID, RIGHT, or LEFT unworkable.

Any suggestions?