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

Suppose we have a dataset of a shop’s Product Name, Quantity, Unit Price, and Total Sold Amount. The amounts are displayed in pounds. Let’s remove the pound signs.

Method 1 – Using Number Format Option

This method will remove the pound (£) sign with just a single click.

Steps:

• Select cells F5:F12.
• From the Home tab, go to the drop-down list in the “Number” section.

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

The pound sign is removed from the selected cells by converting the currency format to number format.

Read More: How to Remove Dollar Sign in Excel

Method 2 – Combining RIGHT and LEN Functions

Steps:

• Select a cell (H5) outside the data table to get the result.
• Enter the following formula:

Where,

• The LEN function will return the total length of 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 the rest of the column.

All the pound signs are removed in the column.

Method 3 – Using Find & Replace Command

Using a keyboard shortcut in Excel’s “Find and Replace” command will remove the pound sign easily.

Steps:

• Press Ctrl+H to open the “Find and Replace” window.

• Press Alt + 0163 to get the pound sign in the “Find what” section.
• Click “Replace All”.

A confirmation window will appear confirming the replacement.

• Click the OK button to continue.

We have successfully removed all the pound signs from the column.

Method 4 – Using VALUE Function

The VALUE function in Excel converts a number into a numeric value.

Steps:

• Choose a cell (H5) to apply the formula.
• Enter the following formula:

• Press Enter.
• Drag the “Fill Handle” down.

We have successfully removed a sign from numbers to get the desired result in a different column.

Method 5 – Using SUBSTITUTE Function

Steps:

• Select a cell (H5) to enter the formula.
• Enter the following formula:

Where,

• The SUBSTITUTE function replaces specific text from the cell (F5).

• Press Enter and drag down the “Fill Handle” to get the output in all the cells in the column.

All the pound signs are gone.

Read More: How to Remove Dollar Sign in Excel Formula

Method 6 – Using REPLACE Function

This method is almost identical to Method 5.

Steps:

• Choose a cell to insert the formula (H5).
• Enter the following formula:

Where,

• The REPLACE function replaces signs or text within a given string.

• Press Enter to get the output in the cell.
• Pull the “Fill Handle” down to copy the formula to the rest of the cells in the column.

The pound signs are removed.

Method 7 – Using Flash Fill Feature

We usually use this feature to fill cells with sequences of the same data, but we can also use it to remove the pound sign from a range of cells.

Steps:

• In column G, enter a couple of the adjacent numbers in column F, but without the pound sign.
• Select cells G5:G6.

After selecting, the “Fill Handle” will appear.

• Pull it down to fill the cells.

The expected output is not visible yet.

• Click the icon on the bottom right of the column and select “Flash Fill”.

The correct result with pound signs removed is returned.

Method 8 – Combining SUBSTITUTE, CHAR, CODE & LEFT Functions

Steps:

• Choose a cell (G5) and enter the following formula:

Where,

• The CHAR function returns a specific character when a valid number is given as 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.

All the pound signs are removed from the table.

Things to Remember

• Sometimes “####” errors may occur due to insufficient space in the cell. Just widen the column to solve these errors.
• Press ALT+0163 to get the pound (£) character easily, as this sign doesn’t appear on most keyboards.
• In Methods 3 and 8, we 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, choose the immediately adjacent column or row to get the output. Otherwise, it won’t work.

Download Practice Workbook

Related Articles

• How to Remove Currency Symbol in Excel
• How to Remove Negative Sign in Excel
• How to Remove Percentage Symbol in Excel
• How to Remove Percentage Symbol in Excel Without Changing Values
• How to Remove Plus Sign in Excel

<< Go Back to Remove Symbol in Excel | Excel Symbols | Learn Excel