Hello Hozefa,
Which option did you use to resolve your issue? Please enlighten us all. Was
the text in the same cell seperated by a space or was it with the numbers in
the cell on the right or left? Was the text the same in all the cells etc.?
----- Original Message -----
From: Hozefa Tambawala
To: accessindia@accessindia.org.in
Sent: Tuesday, February 28, 2012 10:26 PM
Subject: Re: [AI] Help needed to calculate sum in Excel worksheet
Thank you friends for your valuable suggestions.
On 2/28/12, Zujar Shabbir Kanchwala <zujarbri...@gmail.com> wrote:
> Oh Yes!
>
> that's so straight-forward. For example, if the cells contain 100MB,
> 200MB, 300MB, 400MB, etc then press CTRL+H to open the Find and
> Replace dialog box. Enter MB in the Find What edit combo and activate
> the Replace All button. Note the the string MB would be replaced all
> over the worksheet. If you do not wish to replace it all over the
> worksheet then select the cells using GoTo dialog box (CTRL+G) and
> then perform a Replace All.
>
> If you wish to retain the original strings then this is not the right
> option.
>
> Thanks Zoher for telling the list about the easiest way of purging a
> known string from a number of places in the worksheet.
>
> On 2/28/12, zoher <zos...@gmail.com> wrote:
>> 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" <zujarbri...@gmail.com>
>> To: "Mujtaba Merchant" <mujta...@gmail.com>;
>> <accessindia@accessindia.org.in>
>> 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 <mujta...@gmail.com> 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/accessindia@accessindia.org.in/
>>>>
>>>> To unsubscribe send a message to
>>>> accessindia-requ...@accessindia.org.in
>>>> 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/accessindia@accessindia.org.in/
>>>>
>>>> To unsubscribe send a message to
>>>> accessindia-requ...@accessindia.org.in
>>>> 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/accessindia@accessindia.org.in/
>>>
>>> To unsubscribe send a message to
>>> accessindia-requ...@accessindia.org.in
>>> 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/accessindia@accessindia.org.in/
>>
>> To unsubscribe send a message to
>> accessindia-requ...@accessindia.org.in
>> 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/accessindia@accessindia.org.in/
>
> To unsubscribe send a message to
> accessindia-requ...@accessindia.org.in
> 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
>
>
--
"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/accessindia@accessindia.org.in/
To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
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/accessindia@accessindia.org.in/
To unsubscribe send a message to
accessindia-requ...@accessindia.org.in
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
