if the characters are same in text and number then why to use a formula,
just use find and replace command.
----- Original Message -----
From: "Zujar Shabbir Kanchwala" <[email protected]>
To: "Mujtaba Merchant" <[email protected]>;
<[email protected]>
Sent: Tuesday, February 28, 2012 5:01 PM
Subject: Re: [AI] Help needed to calculate sum in Excel worksheet
Hi Mujtaba,
The RIGHT() function returns a text string, you should use the VALUE()
function to get a numeric equivalent for a numeric calculation. For
example, changing your formula to the following is good for numeric
calculations:
=VALUE(RIGHT(A1, 3))
Also, the LEFT() and RIGHT() functions are good when you have a fixed
number of characters to extract and the characters to purge are
distinct. The above formula would not produce correct results for the
following set of values:
BA12 - (RIGHT function returns A12)
CSG2290 - (RIGHT function returns 290)
A7 - (RIGHT function returns A7)
It's better to use the SUBSTITUTE() function if the string to purge is
known:
=SUBSTITUTE(A1, "MB", "")
where A1 contains a value 241MB.
The limitation of the SUBSTITUTE() function is that it would produce
incorrect results if copied for cells with distinct string values to
purge.
One more option is to use the MID() function. This function returns a
string containing number of characters from a specific position. For
example, the below function returns 123:
=MID("ALLL123large", 4, 3)
If the strings to purge are distinct and you know the length then use
REPLACE() function. For example:
=REPLACE("MUM230", 4, 3) returns 230.
=REPLACE("MUM230RS", 4, 3) also returns 230.
The limitation of REPLACE() function when copying it is that the
strings to pirge should be of the same length and start at the same
position.
The use of these functions depends on the situation you are in. For
single values, they always produce the desired result.
On 2/28/12, Mujtaba Merchant <[email protected]> wrote:
Hello Hozefa,
Too challenging for me to answer your question but here is an attempt
which
might work. I urge you to practise this first on a new excel workbook
before
you apply it to the original file in concern.
I am assuming that the text is before or after the numbers and not in
between them in the column cells. Depending on the case that the numbers
are
on the right and are of the same number of characters i.e. that the
numbers
have the same number of digits.
In the cell A1 to A3, type the following:
BAn 122
CAL 586
BOM 856
In the column B cell row 1 type this formula:
=right(A1,3)
Now see what you get in cell B1, if you have done everything correctly
you
should see just the number from the cell A1 which is 122. In the formula
above the number 3 indicates the count of characters from the right from
the
cell referenced which is A1 in this case.
Perhaps this logic can be used to help you to do the sum for the column?
Mujtaba Merchant
Bangalore, India
----- Original Message -----
From: Hozefa Tambawala
To: Accessindia
Sent: Tuesday, February 28, 2012 2:18 PM
Subject: [AI] Help needed to calculate sum in Excel worksheet
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
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
