Extracting Text

[Guest]

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?

 

[Guest]

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

Go to other and type ;

Press ok

 

[Guest]

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

 

[T. Valko]

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),FIND(";",MID(A1,FIND(";",A1,FIND(";",A1)+1)+1,255))-2)

Biff

 

[Guest]

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

=MID(I26,1+(FIND(";",I26,(FIND(";",I26,1)+1))),(FIND(";",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.

 

[Guest]

Try this:

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

 

[Guest]

Another way...

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

 

[T. Valko]

Both of your formulas will pickup the space after Sweep.

Need to subtract 2 at the end.

Biff