Can anyone help me 'clean up' an Excel spreadsheet?

[flower-child]

Our stocktake report has imported all the prices with inverted commas, meaning we can't multiply the number of items against the price.

eg "$24.99"

does anyone have a way I can take away the inverted commas and clean this up?

Pleeease

there are over 1000 products, so manually is not an option.

[Luth6322]

Do a find and replace.

Find "

leave the replace field blank

Select replace all.....hopefully should work a treat. If you have loads of them though it might take a bit of time.

Cheers

Carrie

[onsiteTECHS]

Originally Posted by flower-child

Our stocktake report has imported all the prices with inverted commas, meaning we can't multiply the number of items against the price.

eg "$24.99"

does anyone have a way I can take away the inverted commas and clean this up?

Pleeease

there are over 1000 products, so manually is not an option.

You should be able to do that easily using the Find and Replace feature in Excel:

1. Make a copy of your spreadsheet in case things go wrong!
2. Highlight the column that has the unwanted characters
3. Click on "Edit | Replace"
4. In "Find What" enter an inverted comma (quotation mark?)
5. In "Replace with" leave blank or enter a space
6. Click on "Replace all"

Good luck.

Cheers,
John

[onsiteTECHS]

Originally Posted by Luth6322

Do a find and replace.

Find "

leave the replace field blank

Select replace all.....hopefully should work a treat. If you have loads of them though it might take a bit of time.

Cheers

Carrie

Beat me to it!

[Luth6322]

[flower-child]

thanks all - I already tried the find and replace, but with no success.

It's almost like they (inverted commas) are hidden. You can only see them when you click on the "show calculation steps"

[mnj's buyers group]

I'm am really a complete novice at excel and usually stumble across my answers by mistake.

Has the format of your column been changed some how. Right click on your colums and make sure that the format is for currency.

Hey over a thousand entries anything is worth a try!!

[onsiteTECHS]

Originally Posted by mnj's buyers group

I'm am really a complete novice at excel and usually stumble across my answers by mistake.

Has the format of your column been changed some how. Right click on your colums and make sure that the format is for currency.

Hey over a thousand entries anything is worth a try!!

I agree it's worth checking the cell format on that column, although it's possible that there is a problem with a formula rather than the inverted commas being the cause of the problem - double check that you haven't incorrectly copied a formula and caused a loop or are referencing the wrong cells or something.

If you have no luck you're welcome to email me the document and I'll have a look at it for you.

Best of luck.
John

[Luth6322]

Did a little bit of searching for you.

I'm assuming the import has used a CSV format to import it into Excel, and this is why you have the quotes around the numbers.

From what I can tell you should be able to get rid of this by doing a text to coloumns.

I would also check that the file is actually saved as an Excel file and not a csv or txt file.

Have no idea is this will help, but thats all I could find.

Hope it helps.

Cheers

Carrie

[flower-child]

update

Have tried the text to columns, find and replace, changing to text with no luck

Here's a link to an excerpt (just a couple of lines) as I can't upload an excel document here.

Do your best!