Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting data with ;
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 ; |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting data with ;
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(";",A 1),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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting data with ;
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(";",A 1),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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sorting data with ;
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. -- jason2444 Wrote: 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(";",A 1),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 ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=532067 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ranking query | Excel Discussion (Misc queries) | |||
sorting data and automatic graphs | Charts and Charting in Excel | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Automatically sorting data | Excel Discussion (Misc queries) | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) |