Substitute

The task I had to do: I downloaded certain financial numbers for a company from the BSE site specifically speaking promoter holding for a company. I wanted to calculate the % change in the promoter holding, but on dividing the numbers, excel did not throw up a number, it instead gave up a #VALUE! error. So as I looked at the numbers, I found that the numbers downloaded were as follows 1,01,72,332 and excel did not treat this as a number but as a string because of the commas present. So how could I convert this string to number. Some googling on replacing a part of the string threw up the following two functions

• Replace
• Substitute

Replace did not solve the problem as the name suggest it replaces a part of the string say you entered =Replace("Computer",2,8,"an"), the result would be "Can" as the function replaces the string from the second character to eight character by "an". The format for replace is as follows =Replace(text_in_which_to_replace,start_num,end_num,"with_what_to_replace")

But what I wanted was different i wanted all the commas to be replaced by ""(nothing). So the substitute function came to the rescue. I did a simple thing =substitute("1,01,72,332",","") and it returned what i wanted, a number which I could play around with. The format for substitute is as follows =Substitute(text_in_which_to_replace, text_to_find, text_to_replace)