Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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(+$C71,(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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Logical Test
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(+$C71,(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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Logical Test
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(+$C71,(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use 'CONTAINS' in an IF Logical Test? | Excel Worksheet Functions | |||
Logical test | New Users to Excel | |||
logical test | Excel Worksheet Functions | |||
Logical Test | Excel Worksheet Functions | |||
Logical test | Excel Discussion (Misc queries) |