remove characters from a column

tursiops7 

IMHO, if the number is always only one digit, Text to Columns is the fastest and easiest way.

If few digits, not sure what I'd prefer - Power Query with split as

or CSE formula like

=RIGHT(A1,LEN(A1)-MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1,ROW(INDIRECT("1:"&LEN(A1)))),0)))

 Each has pros and cons.

tursiops7 

In addition, you can use Flash Fill

1. In cell B2, type in Apple and Click Enter

2. cell B3, execute CTRL + E

All the Items will be extracted. See caption below

tursiops7 

Alternatively, if this is a one-time exercise, just enter this formula (assuming your text like 1#Apple) is in A1:

=RIGHT(A1,LEN(A1)-2)

.... and copy it all the way down. If needed, you can copy-paste the whole column as values on top of itself and delete column A.

Load all the data into Power Query and use Split column

1. Click anywhere in the data and execute CTRL + T to format as Table

2. In the Data tab, click on From Table/Range

3. In the Power Query Editor, click on Split Column by Positions

4 In the Split by Positions dialogue box, type in 2 in the box

5. Click OK

All the unwanted characters are removed

Then, you can Close and Load to load the result back to Excel as seen in the caption below