string function

[thanhnguyen]

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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...

 

[thanhnguyen]

Hi Bob,

Many thanks. How to get out max number in column C to display on column
E.

Thanks
Thanh Nguyen

 

[Bob Phillips]

=MAX(C:C)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"thanhnguyen" <[email protected]>
wrote in message
news:[email protected]...

 

[thanhnguyen]

Hi Bob

If value of cell in Coloumn C is max, displayed "Expensive" in column E
and else is "Cheap"

Thanks.

 

[Bob Phillips]

=IF(C1=MAX(C:C),"Expensive","Cheap")

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"thanhnguyen" <[email protected]>
wrote in message
news:[email protected]...

 

[thanhnguyen]

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

 

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...