Excel 2007 paste data, need to convert to numbers

[Tony O]

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.

 

[Don Guillett]

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.

 

[James Ravenswood]

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

 

[joeu2004]

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.

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".