Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
Hi All, I would like to use Excel's string function not VBA to filter out my data as sample. Please help me to do that. Many thanks Thanh Nguyen Ex: input data [A1] AGF: 4388034*73000*(1/16009)=20009150 [A2] BBC: 5600000*39500*(1/17200)=13817228 output data [B1] = AGF [B2] = BBC [C1] =73000 [C2] =39500 [D1]= 20009150 [D2]= 13817228 and I would like to compare all data in column C to filter out max/min number and display Cheap or Expensive -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
B1: =LEFT(A1,FIND(":",A1)-1)
C1: =--MID(A1,FIND("*",A1)+1,FIND("*(",A1)-FIND("*",A1)-1) D1: =--RIGHT(A1,LEN(A1)-FIND("=",A1)) -- HTH Bob Phillips (remove xxx from email address if mailing direct) "thanhnguyen" wrote in message ... Hi All, I would like to use Excel's string function not VBA to filter out my data as sample. Please help me to do that. Many thanks Thanh Nguyen Ex: input data [A1] AGF: 4388034*73000*(1/16009)=20009150 [A2] BBC: 5600000*39500*(1/17200)=13817228 output data [B1] = AGF [B2] = BBC [C1] =73000 [C2] =39500 [D1]= 20009150 [D2]= 13817228 and I would like to compare all data in column C to filter out max/min number and display Cheap or Expensive -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
Hi Bob, Many thanks. How to get out max number in column C to display on column E. Thanks Thanh Nguyen -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
=MAX(C:C)
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "thanhnguyen" wrote in message ... Hi Bob, Many thanks. How to get out max number in column C to display on column E. Thanks Thanh Nguyen -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
Hi Bob If value of cell in Coloumn C is max, displayed "Expensive" in column E and else is "Cheap" Thanks. -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
=IF(C1=MAX(C:C),"Expensive","Cheap")
-- HTH Bob Phillips (remove xxx from email address if mailing direct) "thanhnguyen" wrote in message ... Hi Bob If value of cell in Coloumn C is max, displayed "Expensive" in column E and else is "Cheap" Thanks. -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
Hi Bob, Max("C:C")=0 because of after using MID function it return text no number, and I would like to display Expensive or Cheap only on cell which i max and min not all. Thanks so much, Thanh Nguye -- thanhnguye ----------------------------------------------------------------------- thanhnguyen's Profile: http://www.excelforum.com/member.php...fo&userid=3050 View this thread: http://www.excelforum.com/showthread.php?threadid=54494 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
string function
Hi Nguyen,
It should be numeric not text. If you look at my original formula, you will see I anticipated this =--MID(A1,FIND("*",A1)+1,FIND("*(",A1)-FIND("*",A1)-1) the -- changes the text to numbers. Check that the column is formatted as General, not text. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thanhnguyen" wrote in message ... Hi Bob, Max("C:C")=0 because of after using MID function it return text not number, and I would like to display Expensive or Cheap only on cell which is max and min not all. Thanks so much, Thanh Nguyen -- thanhnguyen ------------------------------------------------------------------------ thanhnguyen's Profile: http://www.excelforum.com/member.php...o&userid=30502 View this thread: http://www.excelforum.com/showthread...hreadid=544941 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to evaluate function as string | Excel Worksheet Functions | |||
string function help | Excel Discussion (Misc queries) | |||
VBA function : How to search a string in another string? | Excel Programming | |||
String function | Excel Programming | |||
String Function | Excel Programming |