How to Remove Pound Sign in Excel (8 Easy Methods)

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!

Remove Pound Sign in Excel


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.

Utilize Number Format Option to Remove Pound Sign in Excel

  • From the drop-down list choose “General”.

Utilize Number Format Option to Remove Pound Sign in Excel

  • 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).

Combine RIGHT, LEN Functions to Remove Pound Sign in Excel

  • 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.

Combine RIGHT, LEN Functions to Remove Pound Sign in Excel

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.

Use Find & Replace Command to Remove Pound Sign in Excel

  • Now, press Alt+0163 to get the pound sign in the “Find what” section.
  • Click “Replace All” to continue.

Use Find & Replace Command to Remove Pound Sign in Excel

  • 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.

Use Find & Replace Command to Remove Pound Sign in Excel

Note: Don’t forget to check the “Things to Remember” column at the end of this article.

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)

Apply VALUE Function to Remove Pound Sign in Excel

  • Click the Enter button.
  • Drag the “fill handle” down.

  • Thus you can remove sign and get the desired result in a different column.

Apply VALUE Function to Remove Pound Sign in Excel

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).

Use SUBSTITUTE Function to Remove Pound Sign in Excel

  • 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.

Use SUBSTITUTE Function to Remove Pound Sign in Excel


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.

Perform REPLACE Function to Remove Pound Sign in Excel

  • 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.

Perform REPLACE Function to Remove Pound Sign in Excel


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.

Use Flash Fill Feature to Remove Pound Sign in Excel

  • 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.

Use Flash Fill Feature to Remove Pound Sign in 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.

Combine SUBSTITUTE, CHAR, CODE & LEFT Functions to Remove Pound Sign in Excel

  • 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.

Combine SUBSTITUTE, CHAR, CODE & LEFT Functions to Remove Pound Sign in Excel


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.


Related Articles

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo