Author Zaheer    Category Formulas     Tags , ,

In Microsoft Excel you are allowed to import or copy data. But if in any instance the data you have imported or copied has included unnecessary words and characters you can replace it with the REPLACE function. Likewise, it also has the REPLACEB function which allows you to replace part of a text or group of text based on a specific byte which could be from a different text string.

The REPLACE function will enable you to replace or change the unnecessary characters or words be substituted with the correct data or with none. The REPLACE function will allow you to simply quickly change a column or columns of data which contains unnecessary characters. To be able to change the data, you must first change the first cell containing the unnecessary characters. After doing so, select and use the fill handle. You can also make use of the copy and paste command or function for all the other cells.

The syntax; the sequence or order of characters for this function is =REPLACE(Old_text,Start_num, Num_chars, Num_bytes, New_text) or =REPLACEB(Old_text,Start_num,Num_bytes,New_text). Old_text argument refers to the old or current text you would want to replace. This can also be the reference cell where the data is imputed or is placed. The specification of the starting position; which is from left of the characters in the Old_text that you would want to replace is referred to as Start_num. Num_chars argument  specifies how many characters will you be replacing or changing which has the command from the position stated in the Start_num argument. The New_text argument in the syntax refers to the new data you will be adding or which you will use to replace the exiting data. This argument can also be left blank if you just want to delete or remove the unnecessary data or character. Num_bytes refers to the specific number of bytes in the Old_text that you will be replacing with the New_text.

Take heed that the REPLACE command is for the languages that utilizes a set of single-byte characters. However, the REPLACEB is used for the languages that contain the set of double-byte characters. These values are affected by the default language set on your computer. The REPLACE function considers and counts every character as 1. This is also true even though the character is in single or double byte no matter what the language used in the default setting is. However, the REPLACEB function counts and consider each character in double-byte as 2. This happens when the default language is permitted to be edited and be in DBCS. The DBCS supports Japanese, Chinese in a simplified form and traditional form, and Korean.

An example of the usage of the function of REPLACE in Excel, first open an Excel worksheet or spreadsheet. On the cell A1 and input the characters: 12#$56&*. After doing so, click on the B1 cell on you spreadsheet, this cell will hold the function or command you will be using. Select the Formulas tab located on the ribbon menu. On the ribbon menu, select Text from the drop down list. Select and click on REPLACE for the dialogue ox of the function to show up. When the dialogue box appears, click on the Old_text line. After doing so, select on the cell where the data you want to change is on.

Now, select on the Start_num line and enter the number 1 which indicates that you wan to change the first character on the cell. Remember that the counting of placement in the call starts from the left. Now, click on the Num_cahrs line and enter the number 3. This argument will replace the first three characters on the source cell. Select on New_text line and type an exclamatory point (!). The first three characters will be replaced with three exclamatory points (!!!). After doing so, hit OK.

The function or command will appear on the B1 cell. If you click the cell the command will appear on it and on the formula bar located on the top of the worksheet. This command will be very useful and helpful for changing certain values or numerical values in a cell. This function can also be used in changing letters in labels and column names.


Post comment