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?

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

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?

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

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