Wasim Akram
Suppose you have a range of data in your Microsoft Excel workbook. In the dataset, you have pound(£) signs in various columns or rows. You can remove those signs one by one cell easily. But it will waste too much time. I have a solution for this to save your time. In this article, I am going to show you how you can remove the pound(£) sign in excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
8 Simple Methods to Remove Pound Sign in Excel
Today I am describing 8 quick and simple methods to remove the pound(£) sign in excel.
Suppose we have a dataset of a shop’s Product Name, Quantity, Unit Price, and Total Sold Amount. The amounts are calculated in pounds. Now we are going to remove the pound sign from the column with some easy tricks. Stay tuned!
1. Utilize Number Format Option to Remove Pound Sign
In this first method, I am explaining the easiest trick to remove the pound(£) sign just by a single click. With the help of the number format option, we will remove the pound(£) sign from the selected column.
Steps:
- Select cells from the dataset. Here I have selected cell (F5:F12).
- Go to the drop-down list in the “Number” format section.
- From the drop-down list choose “General”.
- You will see the pound sign is removed from the selected cells converting the currency format to number format.
Read More: How to Remove Symbol in Excel (8 Ways)
2. Combine RIGHT, LEN Functions to Remove Pound Sign
You can use formulas to remove the pound sign in excel. By using the RIGHT, and LEN functions we will remove the pound sign from the series of cells.
Steps:
- Select a cell (H5) outside the data table to get the result.
- Apply the formula-
= RIGHT(F5,LEN(F5))Where,
- The LEN function will return the total length from the cell (F5).
- The RIGHT function will extract a given number of characters from the cell (F5).
- Press Enter.
- Drag down the “Fill handle” to get the output in all cells.
- As you can see we have successfully removed all the pound sign from the column by using formulas.
Read More: How to Remove Dollar Sign in Excel Formula (2 Easy Ways)
3. Use Find & Replace Command to Remove Pound Sign
The following method is an easy shortcut method to remove the pound sign. Using a keyboard shortcut in excel’s “find and replace” command you can remove pound sign at a glance.
Here we want to remove the pound sign from the “Unit Price” column. Follow the steps below to do so-
Steps:
- Press Ctrl+H to open the “Find and Replace” window.
- Now, press Alt+0163 to get the pound sign in the “Find what” section.
- Click “Replace All” to continue.
- A confirmation window will appear confirming the replacement.
- Hit the OK button to continue.
- Here you can see we have successfully removed all the pound signs from the column.
4. Apply VALUE Function to Remove Pound Sign
The VALUE function in excel converts the number into a numeric value. Using the VALUE function from excel you can remove the pound(£) sign in excel.
Steps:
- Choose a cell (H5) to apply the formula.
- Write the formula down-
=VALUE(F5)
- Click the Enter button.
- Drag the “fill handle” down.
- Thus you can remove sign and get the desired result in a different column.
Read More: How to Remove Plus Sign in Excel (2 Easy Techniques)
5. Use SUBSTITUTE Function to Remove Pound Sign
In order to remove the pound sign, you can apply the SUBSTITUTE function.
Steps:
- Select a cell (H5) to write the formula.
- Apply the formula in the selected cell-
=SUBSTITUTE(F5, "£", "")Where,
- The SUBSTITUTE function replaces specific text from the cell (F5).
- Hit the Enter button and drag down the “fill handle” to get the output.
- Simply you can get the precious result in a different column by deleting all the pound sign.
6. Perform REPLACE Function to Remove Pound Sign in Excel
In the previous method, I used the SUBSTITUTE function to remove the pound sign. This method is almost the same but we will use the REPLACE function instead of the SUBSTITUTE function.
Steps:
- Choose a cell. Here I have selected cell (H5).
- Put the formula down-
=REPLACE(F5,1,0,"")Where,
- The REPLACE function replaces signs or text within a given string.
- Press the Enter button to get the output in the cell.
- Pull the “fill handle” down.
- Well, here we have our result removing the pound sign from the column.
7. Use Flash Fill Feature to Remove Pound Sign in Excel
One of the best features of Excel is the flash fill feature. It is always used by a user to save time. We usually use this feature to fill cells with sequences of the same data. But in this method, I will show you a simple way to remove the pound sign with this feature of excel.
Steps:
- Just by the side of the column type the same numbers without the pound
- Select cells (G5:G6) where we put those numbers.
- After selecting the fill handle will appear.
- Pull the “fill handle” down to fill the cells.
- As you can see we didn’t get the expected output. But don’t worry.
- Click the icon below and select “Flash Fill”.
- Now we got our result by using the flash fill feature of excel.
8. Combine SUBSTITUTE, CHAR, CODE & LEFT Functions to Remove Pound Sign
You can use the combination of SUBSTITUTE, CHAR, CODE, and LEFT functions to remove pound sign in excel.
Steps:
- Choose a cell (G5) and apply the formula below-
=SUBSTITUTE(E5,CHAR(CODE(LEFT(E5))),"")Where,
- The CHAR function gives a specific character when a valid number is given in the input string.
- The CODE function returns a specific code for the first character of a text string.
- Press Enter and drag down the “fill handle” to collect results in all the cells.
- Here we have our list ready removing all the pound signs from the table.
Things to Remember
- Sometimes “####” types of error may occur due to insufficient space in the cell. Just drag the column to solve these errors.
- You can press ALT+0163 to get the pound(£) easily as this sign doesn’t appear on the keyboard.
- In methods 3 and 8, I have used the pound sign manually. These methods won’t work if you use the pound(£) sign from the number format.
- While using the “flash fill” feature to remove signs in a different column. You must choose the immediate side-by-side column or row to get the output. Otherwise, it won’t work.
Conclusion
In this article, I have tried to cover all the simple methods to remove pound sign in excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We, the ExcelDemy team, are always responsive to your queries. Stay tuned and keep learning.
