Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove punctuation mark, Split cells & average accross column
I have data in column like this.
50 50/152 [Blank] 56 150/150 20 [Blank] I want only single numerical or blank in cell. For that I need average of 50/152. I am using formula =(LEFT(A1, FIND("/",A1)-1) + MID(A1, FIND("/",A1)+1, 100)) / 2 for that. But How can I use it with IF so that I can apply to whole column? Question is - To remove punctuation mark from data & make it clean. Than keep only single numerical digit. What to do if there is any other punctuation mark in cell? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove punctuation mark, Split cells & average accross column
Hi,
Am Tue, 6 Nov 2012 23:43:57 -0800 (PST) schrieb Gautam Bhut: I have data in column like this. 50 50/152 [Blank] 56 150/150 20 [Blank] I want only single numerical or blank in cell. For that I need average of 50/152. try: =IF(A1="","",IF(ISNUMBER(FIND("/",A1)),(LEFT(A1,FIND("/",A1)-1)+MID(A1,FIND("/",A1)+1,99))/2,A1)) or select your column = Data = Text to columns with delimiter "/". Then you have to columns with clean data. Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove punctuation mark, Split cells & average accross column
Hey this is not showeing average of both numerical.
i.e. for 960/965 - the formula gives 592.5. What to do? And if I use text to columns than what to do next for 960 965? On Wednesday, November 7, 2012 1:13:57 PM UTC+5:30, Gautam Bhut wrote: I have data in column like this. 50 50/152 [Blank] 56 150/150 20 [Blank] I want only single numerical or blank in cell. For that I need average of 50/152. I am using formula =(LEFT(A1, FIND("/",A1)-1) + MID(A1, FIND("/",A1)+1, 100)) / 2 for that. But How can I use it with IF so that I can apply to whole column? Question is - To remove punctuation mark from data & make it clean. Than keep only single numerical digit. What to do if there is any other punctuation mark in cell? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove punctuation mark, Split cells & average accross column
Hi,
Am Wed, 7 Nov 2012 00:16:57 -0800 (PST) schrieb Gautam Bhut: Hey this is not showeing average of both numerical. i.e. for 960/965 - the formula gives 592.5. I don't know, what is wrong. In my workbook I get the result 962.5 And if I use text to columns than what to do next for 960 965? =IF(A1="","",AVERAGE(A1,B1)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove punctuation mark, Split cells & average accross column
Text to column deletes second numerical with "/"
i.e. 960/965 Text to column shows only 960. What to do? Thanks for Help. On Wednesday, November 7, 2012 1:59:30 PM UTC+5:30, Claus Busch wrote: Hi, Am Wed, 7 Nov 2012 00:16:57 -0800 (PST) schrieb Gautam Bhut: Hey this is not showeing average of both numerical. i.e. for 960/965 - the formula gives 592.5. I don't know, what is wrong. In my workbook I get the result 962.5 And if I use text to columns than what to do next for 960 965? =IF(A1="","",AVERAGE(A1,B1)) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove punctuation mark ' | Excel Programming | |||
Linking Cells accross Workbooks using Column Names | Excel Discussion (Misc queries) | |||
remove punctuation from address | New Users to Excel | |||
Remove Punctuation? | Excel Worksheet Functions | |||
Remove green mark upper left several cells Excel? | Excel Worksheet Functions |