Remove punctuation mark, Split cells & average accross column

[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.

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?

 

[Claus Busch]

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

 

[Gautam Bhut]

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?

 

[Claus Busch]

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

 

[Gautam Bhut]

Text to column deletes second numerical with "/"

i.e. 960/965
Text to column shows only 960.

What to do?

Thanks for Help.