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:

= RIGHT(F5,LEN(F5))

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.

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

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:

=VALUE(F5)

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:

=SUBSTITUTE(F5, "£", "")

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.

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:

=REPLACE(F5,1,0,"")

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:

=SUBSTITUTE(E5,CHAR(CODE(LEFT(E5))),"")

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.