that is OK if the "MB" remains constant throughout all the rows. but if the
characters change from one row to another, what can we do?
Umesha
----- Original Message -----
From: "Zujar Shabbir Kanchwala" <[email protected]>
To: <[email protected]>
Sent: Tuesday, 28 February, 2012 3:09 PM
Subject: Re: [AI] Help needed to calculate sum in Excel worksheet
Hi Hozefa,
Let me elaborate your query before attempting to answer it. You have
an excel worksheet with data in columns A B and C. I assume that there
is data in rows 2 through 51, row #1 contains column titles. Column B
contains alphanumeric data of the form nMB where n is a numeric value.
You need to remove "MB" from the alphanumeric string and use n in a
numeric calculation.
The SUBSTITUTE() function of Excel can help you in this situation.
Listing below the steps to perform:
1. Enter the following formula in cell D2 and hit the ENTER key:
=VALUE(SUBSTITUTE(B2, "MB", ""))
2. Copy the content of cell D2 to your clipboard with CTRL+C (make
sure the cursor is on the cell D2).
3. Press CTRL+G to launch the GoTo dialog box of Excel. Enter the
following cell reference in the Reference edit combo and press ENTER:
D3:D51
4. Paste clipboard content with CTRL+V.
The cells D2 through D51 would now contain numeric values from column
B that can be used in calculations. The following formula in D52
should give you the sum total of the extracted numeric values:
=SUM(D2:D51)
Note that the SUBSTITUTE() function returns a string that cannot
participate in numeric calculations. So, I have used the VALUE()
function to get numeric equivalent of the string.
Hope your query is answered.
On 2/28/12, Hozefa Tambawala <[email protected]> wrote:
Dear friends,
I have an excel file in which columns A to c are filled. Now I want to
calculate the sum of B column. But that column contains both Alfa and
numeric characters. When I calculated the sum of B column, it
displayed the value as zero. So anybody has an idea how to remove Alfa
characters at once from the particular column and multiple rows?
--
"Life is like a piano.
White keys are happy moments
& Black keys are sad moments.
But remember both keys are played together to give sweet music."
"Focus on your abilities, not your disability."
Warm Regards,
Have a nice day
Hozefa...
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
--
Best Regards,
Zujar...
An optimist laughs to forget, whereas a pessimist forgets to laugh!
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes,
please visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
Search for old postings at:
http://www.mail-archive.com/[email protected]/
To unsubscribe send a message to
[email protected]
with the subject unsubscribe.
To change your subscription to digest mode or make any other changes, please
visit the list home page at
http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
