+ Reply to Thread
Results 1 to 14 of 14

How to delete all commas from a spreadsheet?

  1. #1
    Registered User
    Join Date
    11-18-2008
    Location
    none
    MS-Off Ver
    Office 2007
    Posts
    17

    How to delete all commas from a spreadsheet?

    I have a large spreadsheet in Excel 2007. I am converting it to a .csv file to import in to another program. I need to delete all commas from all data.

    When I try to replace all commas (with nothing or with another character), I get the error message "The formula you typed contains an error."

    I have tried various formats (text, general, etc.) and various file types (.xls,.xlsx, .csv) and still get the same error.

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: How to delete all commas from a spreadsheet?

    That's because your formulas argument separator is also comma. Try..
    1. Change your location in Regional and Language Setting dialog box, in Control Panel, to any other country say Russia. This will change default arqument separator in formulas
    2. Than do your Find & Replace as usually.
    3. Reset to your location in Regional and Language Setting dialog box
    Maybe it's the silliest way, but I'd do that
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Registered User
    Join Date
    11-18-2008
    Location
    none
    MS-Off Ver
    Office 2007
    Posts
    17

    Re: How to delete all commas from a spreadsheet?

    Quote Originally Posted by contaminated View Post
    That's because your formulas argument separator is also comma. Try..
    1. Change your location in Regional and Language Setting dialog box, in Control Panel, to any other country say Russia. This will change default arqument separator in formulas
    2. Than do your Find & Replace as usually.
    3. Reset to your location in Regional and Language Setting dialog box
    Maybe it's the silliest way, but I'd do that
    I just tried doing that but unfortunately it didn't work.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,133

    Re: How to delete all commas from a spreadsheet?

    Try Edit > Go to > Special, Constants.

    Then find and replace comma with nothing.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    11-18-2008
    Location
    none
    MS-Off Ver
    Office 2007
    Posts
    17

    Re: How to delete all commas from a spreadsheet?

    Quote Originally Posted by shg View Post
    Try Edit > Go to > Special, Constants.

    Then find and replace comma with nothing.
    The spreadsheet is too large to select all constants. When I tried doing it for a single column, I still received the same error message.

  6. #6
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: How to delete all commas from a spreadsheet?

    How about set your calculation to manual then do what shg suggested. And what the error message is it?
    Switch TO manual in Excel 2003
    Switch to manual in Excel 2007
    Last edited by contaminated; 08-24-2010 at 12:52 PM.

  7. #7
    Registered User
    Join Date
    11-18-2008
    Location
    none
    MS-Off Ver
    Office 2007
    Posts
    17

    Re: How to delete all commas from a spreadsheet?

    It still doesn't work. The error message is "The formula you typed contains an error."
    Last edited by shg; 08-24-2010 at 01:08 PM. Reason: deleted spurious quote

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,133

    Re: How to delete all commas from a spreadsheet?

    How many rows and columns are used in the worksheet?

    How often do you need to do this?

    Do you know how to use macros?

    Please don't quote whole posts -- it's just clutter.

  9. #9
    Registered User
    Join Date
    08-05-2010
    Location
    Around
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: How to delete all commas from a spreadsheet?

    Have you tried simply doing a global search and replace? In Excel 2003, just click Edit > Replace, then enter a comma in the "Find what" field and leave the "Replace with" field blank.

  10. #10
    Registered User
    Join Date
    11-18-2008
    Location
    none
    MS-Off Ver
    Office 2007
    Posts
    17

    Re: How to delete all commas from a spreadsheet?

    The global find and replace gives the same error message.

    I have 50K rows and 60 columns. I will probably be doing this just once. I am somewhat familiar with macros (have modified, never written from scratch).

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,321

    Re: How to delete all commas from a spreadsheet?

    i think youll get that message if there is a formula somewhere on the sheet
    eg if(a1=10,true,false) when you try to remove the commas you'll get an error
    have you copied pasted back values first to remove the formulas ?
    Last edited by martindwilson; 08-24-2010 at 03:55 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  12. #12
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,133

    Re: How to delete all commas from a spreadsheet?

    Try this:
    Please Login or Register  to view this content.
    Last edited by shg; 08-24-2010 at 06:31 PM.

  13. #13
    Registered User
    Join Date
    11-18-2008
    Location
    none
    MS-Off Ver
    Office 2007
    Posts
    17

    Re: How to delete all commas from a spreadsheet?

    Thanks shg - the code worked!

  14. #14
    Registered User
    Join Date
    03-12-2016
    Location
    Los Angeles
    MS-Off Ver
    365
    Posts
    1

    Re: How to delete all commas from a spreadsheet?

    Thanks dude. Magic code worked!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1