Need an expert on MS Excel.

[mattsmusiccenter]

Who's good with Excel?

I've got a new website ready to launch for the shop but there's a line of HTML code that's part of each product description and this one line needs to be deleted from 2500 products. We've exported the entire product catalog to an Excel spreadsheet. This one line of code is exactly the same for each product and it's right at the end of each description. I'm wondering if there's a way I can tell Excel to delete the last 150 characters from the end of the text for each box inside a column.

I'm trying to find an easy way of deleting this code, other than manually doing it to 2500 products.

[DonP]

Someone will chime in who knows macros or VB script, but I use SQL a lot at work. If I had to do this, I'd import the data into a database (DB2, SQL, Access, etc.) and do an update with a substring function.

update database.table set field = substr(field, start position, length)

Then export back out to Excel or wherever it needs to go.

[mattsmusiccenter]

And how long might something like this take?

[Axewielder]

Here's a simple excel-only formula:

=LEFT([string to truncate], LEN([string to truncate]) - [number of chars to remove])

for example, if one record is in column A1, you'd put this formula in B1 :

=LEFT(A1, LEN(A1) - 150)

Then copy that cell all the way down column B

[mattsmusiccenter]

I plugged this in but it didn't do anything.

=LEFT(g17, LEN(g17) - 171)

Am I doing something wrong?

Here's a simple excel-only formula:

=LEFT([string to truncate], LEN([string to truncate]) - [number of chars to remove])

for example, if one record is in column A1, you'd put this formula in B1 :

=LEFT(A1, LEN(A1) - 150)

Then copy that cell all the way down column B

[nhspike]

did you try a simple "find" and replace?
(search for copy/paste the thing you want to get rid of) then in the replace box put in nothing.
Just a thought

[Axewielder]

I plugged this in but it didn't do anything.

=LEFT(g17, LEN(g17) - 171)

Am I doing something wrong?

Didn't you only want to remove 150 chars? That formula will remove 171 chars.

[mattsmusiccenter]

I counted and it's 171 characters that need to be deleted.

[Franx]

did you try a simple "find" and replace?
(search for copy/paste the thing you want to get rid of) then in the replace box put in nothing.
Just a thought

That's exactly what I would have suggested ! Copy the section you want to have deleted and paste it in the "find" field, replace it with "blank" ! That should work !

[mattsmusiccenter]

Sounds easy... can I get a little more detail on the exact steps to do this? I thought I was good with Excel, apparently not good enough though!

That's exactly what I would have suggested ! Copy the section you want to have deleted and paste it in the "find" field, replace it with "blank" ! That should work !

[Axewielder]

I counted and it's 171 characters that need to be deleted.

I'm stumped. That formula is pretty straightforward.

[mattsmusiccenter]

Looks like this should work but it's not doing anything. Did I type the formula correctly?

=LEFT(g17, LEN(g17) - 171)

[Spivonious]

Go up to edit->Replace..., type what you want to delete into the Find field and leave the replace field blank. Then click Replace All. Much easier than messing with formulas or VBA.

[Axewielder]

Looks like this should work but it's not doing anything. Did I type the formula correctly?

=LEFT(g17, LEN(g17) - 171)

Yeah it's right, at least for Office 2003. Make sure auto calculation is enabled (tools\options\calculation). Also make sure the cell with the formula is set to "general" format (right click/format cells).

[anuske9]

I think the find and replace would be the easiest way to do this.

ctrl+f

copy the code that you want to get rid of.

I don't have excel on this computer but it's not too hard, there should be a replace tab or advanced tab.

Then just fill in the "replace with:' as a blank

http://support.microsoft.com/kb/288291