I used the Find and Replace option to solve my query. As other options
suggested didn’t work for me.
On 2/28/12, Mujtaba Merchant <mujta...@gmail.com> wrote:
> 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
>
>
--
"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
