PC Review


Reply
Thread Tools Rate Thread

Excel 2007 paste data, need to convert to numbers

 
 
Tony O
Guest
Posts: n/a
 
      11th Aug 2012
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.
 
Reply With Quote
 
 
 
 
Don Guillett
Guest
Posts: n/a
 
      11th Aug 2012
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.
 
Reply With Quote
 
 
 
 
James Ravenswood
Guest
Posts: n/a
 
      13th Aug 2012
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
 
Reply With Quote
 
joeu2004
Guest
Posts: n/a
 
      13th Aug 2012
"Tony O" <(E-Mail Removed)> 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" <(E-Mail Removed)> 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".

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 objects paste linked into PowerPoint 2007, why doespowerPoint reopen the excel file for each link even of the source excel isalready open? georgeb@stny.rr.com Microsoft Powerpoint 0 21st Nov 2012 02:11 PM
why my Excel paste as text, coworker paste as numbers? =?Utf-8?B?SWFuIEVsbGlvdHQ=?= Microsoft Excel Misc 1 19th Dec 2005 11:32 PM
How to convert Excel imported numbers from text to numbers? =?Utf-8?B?QWxkZW4=?= Microsoft Excel Misc 9 1st Apr 2005 09:51 PM
Re: How do I convert decimal numbers to hexidecimal numbers in Excel? Gord Dibben Microsoft Excel Worksheet Functions 0 18th Sep 2004 12:33 AM
Re: How do I convert decimal numbers to hexidecimal numbers in Excel? Dave Peterson Microsoft Excel Worksheet Functions 0 18th Sep 2004 12:17 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:01 PM.