How To Use Replace Function on Spreadsheet

I have usually use spreadsheet as software to help me on completed my jobs. Is so easily and not hard to use-just open and then type anything on spreadsheet template. There are so many function on the spreadsheet that could we use, so with excel from Microsoft.
But most of that both of them (Spreadsheet and Excel) actually have similar function with little different in several items. But today i don't want to discuss about difference of them (Spreadsheet and Excel). Today i wanna share to "how to replace character or number on excel". And I think there so many trick that you could find on the internet. Usually when we want to replace some or several character or number on spreadsheet, basically we use Find and Replace on the same sheet. Actually if we want use Replace function on Spreadsheet-we could use it for another tricks. below i will show you several Technique that using Replace function on Spreadsheet.
On this article I will show you trick that I have been implemented to my job that related with spreadsheet and also programming. As long as you could use your brain effectively you will find out the way of any issue with spreadsheet on generally. For this time I have been just find tree method to solve the issue on spreadsheet that related with regular case such as Wrong value on Cell, Error value on Column and etc.

1. Replace Function on Character or number.

Basically most people has know about this function just using click Edit Menu on Excel than Choose Replace or with the short way you just click on keyboard Ctrl+H. Then Replace dialog box will show just like picture below. Block cell, column or row that you want to replaced. Then on find what text box type object that you want to change. Then on the replace with text box type type object as substitute from the original object on Find what text box. Then press "OK" Button. Then as the result, the original object (character or number) has been replaced with new one. I will give you sample to explain about this.
A. Type value on Cell A1 with "Manggo" ( without Double Quotes)  and We want to replace with "Tanggo" ( withoutDouble Quotes)

B. And press shortcut button on keyboard Just Press (Ctrl+H), then Replace Dialog Box will show. Make sure that when you on position of Press button on keyboard (Ctrl+H) and Cell A1 is still on selected position.

C. Then on Find what text box, insert on it by typing a word "Manggo" ( without Double Quotes). And on the Replace With text box, insert on it by typing a word "Tanggo" ( without Double Quotes).

E. And the last step is press Replace Button, and Value on Cell A1 will change from "Manggo" became "Tanggo"


2. Replace Function on Link 

This is second level from point number one, because we must really pay attention with the link of your sheet that we want to replaced. Just focus on the old link and selected the link with block all link and then replace with new one. after that press "OK" Button and then the result is show new value with new link. You could see the tutorial below.
On this Replace function using Link, I will create sample link between Sheet1 and Sheet2. I will connected value on Sheet1 from Sheet2 and then replace it with new one.


A. Make there are minimum two sheets on your worksheet just like below. In this case on Sheet2 there is value on Cell A1  with "Bruto" and on the Cell A2 containt "Manggo". Inside on Sheet1  on the position of A1 have value "Bruto" with the actual link formula is "=Sheet2!A1"( without Double Quotes). I want to replace cell A1 value "Bruto" to "Manggo" on Sheet1

B. Make sure you have position on Sheet1. Then block or selected cell A1. It will show link that connected to Cell A1 on Sheet2. Press shortcut button on keyboard Just Press (Ctrl+H), then Replace Dialog Box will show


C. Then on Find what text box, insert on it by typing a word "Sheet2!A1" without Double Quotes.

D. And on the Replace Withtext box, insert on it by typing a word "Sheet2!A2" without Double Quotes


E. And the last step is press Replace Button, and Value on Cell A1 from Sheet1 will change from "Bruto" became "Manggo" by replacing link from "Sheet2!A1" into "Sheet2!A2"

3. Replace Function on Formula

On this trick you will learn about "how to replace formula on spreadsheet". the method is just like point number one and two. the key is focus on the formula on spreadsheet which you have to replace. be careful to the formula, If you wrong replace of it, the result if become error. on this sample, i have been using "VLOOKUP"  and  "IF" formula. And little people know about this function. Ok let's try Replace "VLOOKUP" formula.

A. I have been created spreadsheet Table which I name it with Data1. There three header column (No, Value, and Rank) Just like picture below. I have insert data with that contain formula. We could see that on Column C there is error value that cause of wrong formula.

B. On the column Rank there is value that contain error or with show "#VALUE!" (because of wrong formula because using AND formula the right formula is using IF formula). And to fix that than replace with same function with the right function.

C. Just selected column that content of error value (#VALUE!). When block, the formula will show.


D. Press shortcut button on keyboard Just Press (Ctrl+H), then Replace Dialog Box will show. Make sure that when you on position of Press button on keyboard (Ctrl+H) and Column C is still selected is still on selected position.


E. Then on Find what text box, insert on it by typing a word "AND" ( without Double Quotes).

F. And on the Replace With text box, insert on it by typing a word "IF" ( without Double Quotes) and make sure that you have Selected column that have error value (#VALUE!) just like picture below.


G. And the last step is press Replace All Button, and Value on Column C will change from #VALUE! with right value "GREAT" that you can see with result of clearly right value.


Thank you for read this trick see you on my next article. If you any question about this trick or other trick, don't be hesitate to contact me through comment. 

0 Response to "How To Use Replace Function on Spreadsheet"

Post a Comment