Home – New Forums Other discussions Can anyone help me ‘clean up’ an Excel spreadsheet?

  • This topic is empty.
Viewing 8 posts - 16 through 23 (of 23 total)
  • Author
    Posts
  • #1070032
    bridiej
    Member
    • Total posts: 1,097
    Up
    0
    ::

    Having had a play with it and done some Googling I think you have two options:

    1. Retype the figures in manually.
    2. Clean up the CSV file before importing it into Excel (probably the quicker and easier option, see this factsheet)

    Hope that helps :)

    #1070033
    Five Star PA
    Member
    • Total posts: 64
    Up
    0
    ::

    Ok I worked it out! Have to say weird thing I’ve come across in awhile!

    What I did is convert the excel file to a csv.

    then I opened an MS Access new database and imported this csv file. Once imported I change the settings of the columns to be number and currency formats and then created a formula and it worked.

    If for some reason you then needed to view this in excel you could always export it as excel.

    Hope that helps!

    Meredith
    http://www.fivestarpa.com

    #1070034
    Brynn
    Member
    • Total posts: 1
    Up
    0
    ::

    I have had a quick look at the data you posted.

    If you replace the formula in cell G3 with this it should work:

    =MID(E3,2,(LEN(E3)-2))*D3

    #1070035
    SalenaKnight
    Member
    • Total posts: 604
    Up
    0
    ::

    thanks for the answers – off to try and will let you know the outcome.

    Ok Brynn, I have NO idea what your equation means (and I thought I knew my way around Excel) but whatever you typed worked!!

    I am extremely thankful to you and others – we’ll work on cleaning up the csv file for next time.

    #1070036
    mnj’s buyers group
    Member
    • Total posts: 17
    Up
    0
    ::

    I have been watching this post to see who could fix it the best. Very impressive efforts to see so many people try to fix a strangers problems.

    I suppose that says exactly what FS is all about.

    Glad I’m a newbie!

    #1070037
    HBTRADE
    Member
    • Total posts: 125
    Up
    0
    ::

    I saved it a a CSV, closed & re-opened it in Excel.
    In this case, there was a ÿ character in the $ column.
    select such a cell, then in the address bar copy 1 x ÿ
    find and replace ÿ with
    save as xls or xlsx depending on your version.
    saving as csv only works with single page workbooks, so work with a copy & do 1 page at a time.

    have fun.

    #1070038
    SalenaKnight
    Member
    • Total posts: 604
    Up
    0
    ::
    HBTRADE, post: 88241 wrote:
    I saved it a a CSV, closed & re-opened it in Excel.
    In this case, there was a ÿ character in the $ column.
    select such a cell, then in the address bar copy 1 x ÿ
    find and replace ÿ with
    save as xls or xlsx depending on your version.
    saving as csv only works with single page workbooks, so work with a copy & do 1 page at a time.

    have fun.

    Thanks HBtrade – I’ve book marked this post for future reference and will have a go at your suggestion as well.

    Thanks again to everyone.

    #1070039
    Calcul8or
    Participant
    • Total posts: 481
    Up
    0
    ::

    Hi Flowerchild

    Not sure how you went with your quotation mark problem, but I have encountered this sort of thing several times.

    If you are comfortable in send me a copy of your file, I will fix it for you, or alternatively, if you could just let me know which column the affected data is in, I will send you a “fix” spreadsheet that you can use to correct the problem.

    Programmer. Analyst. Nerd. Calcul8ors.com.au Custom Software & Collaboration
Viewing 8 posts - 16 through 23 (of 23 total)
  • You must be logged in to reply to this topic.