Logical Test

[AFA]

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]

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]

Hi yshridhar
it is working & many thanks for sharing