Excel 2007 paste data, need to convert to numbers

Discussion in 'Microsoft Excel Discussion' started by Tony O, Aug 11, 2012.

  1. Tony O

    Tony O Guest

    I am working with Excel 2007. I am pasting Excel data from a network
    drive to my computer.

    Every time I do this, I have to convert the data in three columns to
    numbers (Excel shows what looks like
    a traffic sign with an exclamation point inside of it). This process
    can take ten minutes.
    I have to convert these columns to numbers for my vlookups.

    Is there a way that I can have the numbers in these three columns
    formatted automatically in the "number format"
    so that I do not have to go through the process everyday with the sign
    with the exclamation point? Please explain.
     
    Tony O, Aug 11, 2012
    #1
    1. Advertisements

  2. Tony O

    Don Guillett Guest

    On Saturday, August 11, 2012 5:50:14 AM UTC-5, Tony O wrote:
    > I am working with Excel 2007. I am pasting Excel data from a network
    >
    > drive to my computer.
    >
    >
    >
    > Every time I do this, I have to convert the data in three columns to
    >
    > numbers (Excel shows what looks like
    >
    > a traffic sign with an exclamation point inside of it). This process
    >
    > can take ten minutes.
    >
    > I have to convert these columns to numbers for my vlookups.
    >
    >
    >
    > Is there a way that I can have the numbers in these three columns
    >
    > formatted automatically in the "number format"
    >
    > so that I do not have to go through the process everyday with the sign
    >
    > with the exclamation point? Please explain.


    There may be an easier way. Send your file to dguillett @gmail.com with a complete explanation and esamples of the process and FINAL result with your lookup formulas.
     
    Don Guillett, Aug 11, 2012
    #2
    1. Advertisements

  3. This is for a single coumn, adapt for the other two columns. If the data is in column A, then in another column enter:

    =MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10^(300-ROW($1:$300))),2,300)

    and copy down. This is an array formula that must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.

    If you would rather convert the values in place, then a VBA macro would be required:

    Sub numerifyy()
    For Each r In Selection
    v = r.Value
    L = Len(v)
    s = ""
    For i = 1 To L
    ch = Mid(v, i, 1)
    If ch Like "*[-0-9]*" Or ch = "." Then
    s = s & ch
    End If
    Next
    If Len(s) > 0 Then
    r.Value = --s
    End If
    Next
    End Sub
     
    James Ravenswood, Aug 13, 2012
    #3
  4. Tony O

    joeu2004 Guest

    "Tony O" <> wrote:
    > Is there a way that I can have the numbers in these three
    > columns formatted automatically in the "number format"
    > so that I do not have to go through the process everyday
    > with the sign with the exclamation point?


    First, it is easy to get rid of those nuisance "sign with the exclamation
    point". Simply disable "background error checking".

    In Excel 2007, click on the Office Button (upper left), then Excel Options
    (lower right), then Formulas. Click on the box next to "Enable background
    error checking" to remove the checkmark. Then click on Reset Ignored
    Errors, then OK.

    Those error-checking warning symbols are usually just a nuisance. Excel
    tries to highlight "inconsistencies" that __might__ indicate data entry
    errors. But in my experience, 99 times out of 100, there is nothing wrong
    at all.


    "Tony O" <> wrote:
    > I am pasting Excel data from a network drive to my computer.
    > Every time I do this, I have to convert the data in three
    > columns to numbers


    The description is not sufficient to help you, unless we make some lucky
    guesses.

    It should not matter that the data are copied from "a network drive". What
    does matter is: what application are you copying the data from? A text
    file? An Excel, MSWord or other structured document? A webpage or saved
    HTML file?

    And exactly what do you mean by "pasting Excel data"? I interpret that
    literally to mean pressing ctrl+V or clicking on Paste in some menu. Is
    that what you mean?

    Also, what exactly do you do to correct the problem? Re-type the numbers
    manually? Just press F2, then Enter? Something else altogether (what)?

    Finally, exactly what form are the numbers? Simple integers or decimal
    numbers? Dates? Give us some examples of numbers that cause problems and
    numbers that don't. Is there any pattern?

    Answering those questions might help us guess what the problem is.

    Off-hand, my guess is: the numbers contain spaces, most likely non-breaking
    spaces (HTML &nbsp). Alternatively, perhaps the cells you are copying into
    are formatted as Text initially.

    Ideally, it might be sufficient to ensure that the cells are formatted as
    General before pasting into them.

    Alternatively, it might be sufficient to use the Text To Columns feature to
    do the conversion. For each column of numbers, select the column (or range
    of cells) -- the selection can include good numbers as well as bad ones.
    Click on Data, then Text To Columns. Then follow the dialog boxes, clicking
    on Next each time.

    If that corrects the problem, it might be possible to do effectively the
    same thing using an Import wizard when you paste the data. Instead simply
    pasting (pressing ctrl+V), right-click and click on Paste Special.

    But sometimes you are not presented with an Import wizard.

    If Text To Columns does not work, it might help to paste into a Notepad or
    equivalent text file editor (MS Wordpad?) first, then copy and paste from
    Notepad into Excel. That removes some of the "structure" of the data, which
    Excel might be preventing Excel from interpreting the data as numbers. It
    also converts non-breaking spaces (HTML &nbsp) into normal spaces, which
    might not confuse Excel as much.

    However, there is a downside to that: it also might change Excel's
    interpretation of other columns that you are pasting.

    Finally, if the problem is indeed non-breaking spaces (HTML &nbsp) and
    nothing above works, you might have to remove the NBSPs and convert the text
    to numbers with a formula.

    If the original data are in A1:A1000, enter the following formula into a
    parallel column (e.g. X1) and copy down:

    =--TRIM(SUBSTITUTE(A1,CHAR(160),""))

    If you get a #VALUE error, the numbers have not been converted successfully,
    and there is yet-another problem to deal with.

    Otherwise, copy the new column (e.g. X1:X1000), and use paste-special-value
    to overwrite the original data in A1:A1000. You can delete the contents of
    the helper column (X1:X1000).

    There are other possible solutions, depending on the circumstances.

    If none of the suggestions above remedies the problem, I suggest that you
    can upload an example Excel file (devoid of any private data) that
    demonstrates the problem to a file-sharing website and post the "shared",
    "public" or "view-only" link (aka URL; http://...) in a response here. The
    following is a list of some free file-sharing websites; or use your own.

    Box.Net: http://www.box.net/files
    Windows Live Skydrive: http://skydrive.live.com
    MediaFire: http://www.mediafire.com
    FileFactory: http://www.filefactory.com
    FileSavr: http://www.filesavr.com
    RapidShare: http://www.rapidshare.com

    (I like box.net. It is simple to use; and it does not encumber the download
    process.)

    The example Excel file should contain the data just after you "paste Excel
    data from a network drive".
     
    joeu2004, Aug 13, 2012
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. How do I convert copy and paste from web to data.

    , Jun 14, 2005, in forum: Microsoft Excel Discussion
    Replies:
    11
    Views:
    345
    David McRitchie
    Jun 18, 2005
  2. Paul Mars

    numbers are not numbers in excel xp

    Paul Mars, Sep 15, 2006, in forum: Microsoft Excel Discussion
    Replies:
    5
    Views:
    169
    Ron Rosenfeld
    Sep 15, 2006
  3. Guest

    How to convert numbers using dots into numbers using commas

    Guest, Dec 5, 2006, in forum: Microsoft Excel Discussion
    Replies:
    3
    Views:
    533
    Guest
    Dec 9, 2006
  4. Guest

    How to paste long numbers eg 210000000000026324 into Excel

    Guest, Mar 29, 2007, in forum: Microsoft Excel Discussion
    Replies:
    1
    Views:
    444
    JE McGimpsey
    Mar 29, 2007
  5. Jambar

    Need to convert the numbers with tolerances

    Jambar, Jan 9, 2008, in forum: Microsoft Excel Discussion
    Replies:
    4
    Views:
    129
    Jambar
    Jan 10, 2008
Loading...

Share This Page