sorting data with ;

[jason2444]

have a file in one column each cell has something like this V8; 5.4;
GAS;FI;MFI;S;3;
need to write a formula in another cell to get V8; 5.4; GAS how do I get it
to grab everything to the left of the 3rd ;

[Bryan Hessey]

to the left:

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

to the right:

=MID(A1,FIND(";",MID(A1,FIND(";",MID(A1,FIND(";",A1),99))+1,99))+FIND(";",MID(A1,FIND(";",A1)+1,99))+FIND(";",A1)+3,99)


or you could select the column and do Data, Text to Columns, delimited on ; and then join the relavant cells together

--

have a file in one column each cell has something like this V8; 5.4;
GAS;FI;MFI;S;3;
need to write a formula in another cell to get V8; 5.4; GAS how do I get it
to grab everything to the left of the 3rd ;

[jason2444]

I need it to only grab everything to the left of the specified ; without
setting a number of characters to grab after the ;
because I will need different amount of characters per line
V8; 5.7; GAS;CARB;4BBL;N;J; V8; 5.7; GAS
V8; 5.7; Diesel;CARB;4BBL;N;J; V8; 5.7; Die

can this be done?

"Bryan Hessey" wrote:

>
> to the left:
>
>
> =MID(A1,1,FIND(";",MID(A1,FIND(";",MID(A1,FIND(";",A1),99))+1,99))+FIND(";",MID(A1,FIND(";",A1)+1,99))+FIND(";",A1)+1)
>
> to the right:
>
>
> =MID(A1,FIND(";",MID(A1,FIND(";",MID(A1,FIND(";",A1),99))+1,99))+FIND(";",MID(A1,FIND(";",A1)+1,99))+FIND(";",A1)+3,99)
>
>
> or you could select the column and do Data, Text to Columns, delimited
> on ; and then join the relavant cells together
>
> --
>
> jason2444 Wrote:
> > have a file in one column each cell has something like this V8; 5.4;
> > GAS;FI;MFI;S;3;
> > need to write a formula in another cell to get V8; 5.4; GAS how do I
> > get it
> > to grab everything to the left of the 3rd ;
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=532067
>
>

[Bryan Hessey]

In the column that you wish the result to be, (say column D)

in cell D1 post the formula shown for 'to the left' - ie:

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

and formula-drag this to the end of your data

this should provide what you want.

--

I need it to only grab everything to the left of the specified ; without
setting a number of characters to grab after the ;
because I will need different amount of characters per line
V8; 5.7; GAS;CARB;4BBL;N;J; V8; 5.7; GAS
V8; 5.7; Diesel;CARB;4BBL;N;J; V8; 5.7; Die

can this be done?

"Bryan Hessey" wrote:

>
> to the left:
>
>
> =MID(A1,1,FIND(";",MID(A1,FIND(";",MID(A1,FIND(";",A1),99))+1,99))+FIND(";",MID(A1,FIND(";",A1)+1,99))+FIND(";",A1)+1)
>
> to the right:
>
>
> =MID(A1,FIND(";",MID(A1,FIND(";",MID(A1,FIND(";",A1),99))+1,99))+FIND(";",MID(A1,FIND(";",A1)+1,99))+FIND(";",A1)+3,99)
>
>
> or you could select the column and do Data, Text to Columns, delimited
> on ; and then join the relavant cells together
>
> --
>
> jason2444 Wrote:
> > have a file in one column each cell has something like this V8; 5.4;
> > GAS;FI;MFI;S;3;
> > need to write a formula in another cell to get V8; 5.4; GAS how do I
> > get it
> > to grab everything to the left of the 3rd ;
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=532067
>
>