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

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

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

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

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

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

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

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