Remove punctuation mark, Split cells & average accross column

  • Thread starter Thread starter Gautam Bhut
  • Start date Start date
G

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?
 
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.
Click to expand...

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
 
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?
 
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.
Click to expand...

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?
Click to expand...

=IF(A1="","",AVERAGE(A1,B1))


Regards
Claus Busch
 
Text to column deletes second numerical with "/"

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

What to do?

Thanks for Help.
 
Back
Top