remove characters from a column

[tursiops7]

I have a column with about 800 rows which each contain a symbol, number, and text.  Here's an example "#1Apple".

How do I keep only the alpha characters and delete the rest.  So, the above example would be "Apple".

Thanks in advance!

Connie

[#2]

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

[#3]

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

[#4]

@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

[#5]

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