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
