Tuesday, July 19, 2011

VBA Replace function

Syntax
Replace(cString , cFind , cReplaceWith)

Example

Replace(cString, "A","a")

The above replaces all occurrences of  capital "A" with small "a" in the cString variable.

Remarks:-
The VBA Replace function is very useful for manipulating Excel data at runtime. In certain cases, where the Excel data is generated from 3rd party softwares, we faced certain issues. Example, the Amount was displayed in TEXT format and included comma separator like 1,50,000.00. We used the VBA Replace function to remove the commas and then convert the value to Numeric.

No comments: