Use the SUBSTITUTE function when you want to replace text based on its content not position. In the first argument value you supply a number or date to be converted to text or a reference to the cell containing a numeric value. Use these formulas for simple calculations.
You can use the Format Cells dialog to find the other available format codes.
I do not want to concatenate a string or show the value like Some Text. When is the Excel TEXT Function required. Use dollar signs to keep one cells formula the same regardless of where it moves. So TEXT formula in Excel becomes TEXT B2 Now if we want to make it more readable by separating the country code then we will change the format code by simply placing a hyphen just after two hashes.