Logical Test

I have extracted text data format thus i cant perform any maths calculation
on the data. Thus i tried this logical test to convert the data into number.

cell A cell B cell C
cell D cell E Cell F
4101001 NET SALES/PRODUCTS 172.641 172 641 172,641
4102001 NET SALES/MERCHAND. 641 641 0
6410

Cell C should read as 172,641 and 641. Since the value is text format,
firstly i tried to pull the interger from 172.641 and mod .641 and which
later combine these two result into number and reads as 172,641.

i write this formula on respective cells,
Cell D =INT(+$"Cell C")*1
Cell E =IF(+$C7>1,(MOD(+$C7,1)*1000))
Cell F= =(+O7&P7)*1 note *1 in formula to convert the text to number

somehow, if the value in cell C is only 641, the result in cell F is 6410.
Anyone can suggest the solution for a formula to suit these 2 situation?
thanks in advance






4102001 NET SALES/MERCHAND. 672

yshridhar
Thu May 08 00:04:00 CDT 2008

One way to convert 172.641(text) to 172,641 (numeric)
=--SUBSTITUTE(A1,".","")
A1 = 172.641
change the cell format to as per your requirement.
Best wishes
Sreedhar
"AFA" wrote:

> I have extracted text data format thus i cant perform any maths calculation
> on the data. Thus i tried this logical test to convert the data into number.
>
> cell A cell B cell C
> cell D cell E Cell F
> 4101001 NET SALES/PRODUCTS 172.641 172 641 172,641
> 4102001 NET SALES/MERCHAND. 641 641 0
> 6410
>
> Cell C should read as 172,641 and 641. Since the value is text format,
> firstly i tried to pull the interger from 172.641 and mod .641 and which
> later combine these two result into number and reads as 172,641.
>
> i write this formula on respective cells,
> Cell D =INT(+$"Cell C")*1
> Cell E =IF(+$C7>1,(MOD(+$C7,1)*1000))
> Cell F= =(+O7&P7)*1 note *1 in formula to convert the text to number
>
> somehow, if the value in cell C is only 641, the result in cell F is 6410.
> Anyone can suggest the solution for a formula to suit these 2 situation?
> thanks in advance
>
>
>
>
>
>
> 4102001 NET SALES/MERCHAND. 672
>
>
>

AFA
Mon May 12 20:56:00 CDT 2008

Hi yshridhar
it is working & many thanks for sharing

"yshridhar" wrote:

> One way to convert 172.641(text) to 172,641 (numeric)
> =--SUBSTITUTE(A1,".","")
> A1 = 172.641
> change the cell format to as per your requirement.
> Best wishes
> Sreedhar
> "AFA" wrote:
>
> > I have extracted text data format thus i cant perform any maths calculation
> > on the data. Thus i tried this logical test to convert the data into number.
> >
> > cell A cell B cell C
> > cell D cell E Cell F
> > 4101001 NET SALES/PRODUCTS 172.641 172 641 172,641
> > 4102001 NET SALES/MERCHAND. 641 641 0
> > 6410
> >
> > Cell C should read as 172,641 and 641. Since the value is text format,
> > firstly i tried to pull the interger from 172.641 and mod .641 and which
> > later combine these two result into number and reads as 172,641.
> >
> > i write this formula on respective cells,
> > Cell D =INT(+$"Cell C")*1
> > Cell E =IF(+$C7>1,(MOD(+$C7,1)*1000))
> > Cell F= =(+O7&P7)*1 note *1 in formula to convert the text to number
> >
> > somehow, if the value in cell C is only 641, the result in cell F is 6410.
> > Anyone can suggest the solution for a formula to suit these 2 situation?
> > thanks in advance
> >
> >
> >
> >
> >
> >
> > 4102001 NET SALES/MERCHAND. 672
> >
> >
> >