Deleting Text In An Excel Cell

[andy-hughes]

I have managed to copy my MP3 list onto an Excel sheet -

Each title has 'A E:\' in front of it - is there an easy way of deleting that piece of text from each line without doing it individually?

Thanks.

[Stephen_G]

Does your version still have the Find and Replace option? You could select all the offending cells and type in what you want to delete. My only concern is that you might also delete any Capital A's or E's. I don't have a working version so I can't test my theory.

[JimF]

Assuming your list begins in cell A1,

1) type the following into B1:
=RIGHT(A1, LEN(A1)-5)
and press Enter

2) Copy the formula all the way down.

3) Highlight all of the new data in the B column and press Ctrl-C to copy

4) Right-click in cell B1, select Paste Special, select Values and click OK.

[Atheist]

I don't know what you mean by 'each title'.
Could the E:\ be referring to a link with another spreadsheet on the E drive?

[New Judge]

First, make a copy of your file!!!

If your first entry is in cell a1, enter in another cell (say e1):

=REPLACE(A1,1,4,"")

This means replace the contents of cell a1 from the first character to the fourth character with nothing (that's what the "" denotes).

You need to make sure that the bit you don't want is indeed four characters (including any blank spaces). If it is not, adjust the figure 4 in the formula as required.

Copy this down to do the same for all your entries. This will give you in column 'E' what you want in Column A so simply replace column A with the contents of Column E.

[andy-hughes]

Thanks guys - in case the forumas offered are varied by this -

Each line starts with A then there are 19 spaces, and then it has E:\A followed by the title.

If anyone can re-advise with that additional info, that would be much appreciated.

Do I type the formula in the 'B' column and then copy?

Thanks!

[JimF]

So amend the formula in step 2) to

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

[bobbinwales]

First as others have said take a copy.
Then.
Best to insert a new column after column A and then copy in as NJ and jim have said altho you'll need to vary there instructions now based on your new info on spaces/characters. Just try some different ways
eg change newjudges to something like REPLACE(A1,1,24,"")

[Buenchico]

If you can't get any of the above to work, try starting again by using TagScanner to create a .csv file which you can then import into Excel:
https://www.xdlab.ru/en/download.htm

Assuming that you're using a 64-bit machine, as almost everyone is these days, use the third download link in the list.

Although there's a link to the full manual available on that page, the instructions (under 'Tag Scanner', obviously) on this page are probably better for your needs:
https://www.ilovefreesoftware.com/04/featured/free-software-to-bulk-export-id3-tags-to-csv-html.html

[bobbinwales]

Sometimes its easier for me to copy a column into word and fiddle around with it. Maybe in Word use find and replace to replace "
A E:\" with " "

[Tuvok]

Do you only have 1 column?

If so do this (but on a copy of the original file just in case anything mucks up)

Use the Find and Replace option to replace E:\ with another character - choose a character that is not used in your list eg. the Euro symbol etc.

Replace All.

Go to the Data menu option in the ribon

Select the column you want to work on. The next column MUST be empty

Now select Text to Column

Select Delimited

Put the Euro symbol (or whatever you used) in the Other box

Click Next/Ok/Finish etc until you end up with your text separated into 2 columns.

Now you can remove the first column that should just be the A and 19 spaces

[THECORBYLOON]

After making a copy, I would just copy from "A" to the start of the title (but not including the first letter of the title) and then highlight the column with the titles.

Choose the replace option, paste what you copied in the find box and then click on replace all.

[Peter Pedant]

thanks JimF
I knew there would be something not so well known in excel

[andy-hughes]

Sorted - many thanks all for your expertise and patience.