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

This topic is empty.

Viewing 15 posts - 1 through 15 (of 23 total)

1 2 →

Author

Posts
August 16, 2011 at 8:57 am #974741
SalenaKnight
Member
- Total posts: 604
Up
0
::
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.
August 16, 2011 at 9:16 am #1070018
Luth6322
Member
- Total posts: 82
Up
0
::
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
August 16, 2011 at 9:18 am #1070019
John C.
Member
- Total posts: 439
Up
0
::
flower-child, post: 87218 wrote:
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:

Make a copy of your spreadsheet in case things go wrong!

Highlight the column that has the unwanted characters

Click on “Edit | Replace”

In “Find What” enter an inverted comma (quotation mark?)

In “Replace with” leave blank or enter a space

Click on “Replace all”

Good luck.

Cheers,
John
August 16, 2011 at 9:19 am #1070020
John C.
Member
- Total posts: 439
Up
0
::

Luth6322, post: 87220 wrote:

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!
August 16, 2011 at 9:23 am #1070021
Luth6322
Member
- Total posts: 82
Up
0
::
August 16, 2011 at 10:56 am #1070022
SalenaKnight
Member
- Total posts: 604
Up
0
::
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”
August 16, 2011 at 11:25 am #1070023
mnj’s buyers group
Member
- Total posts: 17
Up
0
::
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!!
August 16, 2011 at 12:02 pm #1070024
John C.
Member
- Total posts: 439
Up
0
::

mnj’s buyers group, post: 87239 wrote:

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
August 16, 2011 at 12:05 pm #1070025
Luth6322
Member
- Total posts: 82
Up
0
::
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
August 17, 2011 at 1:38 am #1070026
SalenaKnight
Member
- Total posts: 604
Up
0
::
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!
August 17, 2011 at 2:02 am #1070027
cjt
Member
- Total posts: 4
Up
0
::

flower-child, post: 87281 wrote:

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!

Hi Flower-child
That seems to be a bad link.

“Sorry, the page (or document) you have requested is not available.

Please check the address and try again. “
August 17, 2011 at 2:03 am #1070028
SalenaKnight
Member
- Total posts: 604
Up
0
::
thanks CJT – I hadn’t adjusted privacy settings. Should be fine now
August 17, 2011 at 2:25 am #1070029
bridiej
Member
- Total posts: 1,097
Up
0
::
I’m slightly confused as the price shows up as $24.99

Where are the inverted commas?
August 17, 2011 at 3:08 am #1070030
SalenaKnight
Member
- Total posts: 604
Up
0
::

bridiej, post: 87287 wrote:

I’m slightly confused as the price shows up as $24.99

Where are the inverted commas?
they are hidden – you only see them when you view the error message
August 17, 2011 at 3:36 am #1070031
The Copy Chick
Member
- Total posts: 963
Up
0
::
I Googled ‘convert excel text to numbers’ and came up with some great ideas, but for some reason, none of them worked. The best I could manage was to convert the text to general, but it made no difference to the end result.

Perhaps you could try these in the original doc and see if you have any success.

Good luck!
Author

Posts