Solved

Convert cells in excel to text to number

Posted on 2009-04-09
8
344 Views
Last Modified: 2012-05-06
Hi,
im exporting data to an excel sheet. Im adding som e formulas though vb.net to some cells.
But when i export the data the cells are store as text. I need to store certain cells as numbers.

How can i convert the cells to numbers?

thanks
0
Comment
Question by:arcross
8 Comments
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24105460
while storing excel formulas from vb.net or any other language, use equal sign (=) before the formula then it must calculate automatically.
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 24105466
Hi,

If you want to use formulae then this is the form where A1 contains the text string "23":

=VALUE(A1)

which will then evaluate to the value 23.

Alan.

0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24105468
How do you export ? Can you show some code?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 12

Accepted Solution

by:
Alan3285 earned 500 total points
ID: 24105480
Hi,

If you want to use VBA then this (adjust the worksheet and range to suit of course).

Alan.
Sub test()
 
    For Each mycell In ActiveSheet.UsedRange
    
        mycell.Value = CLng(mycell.Value)
    
    Next
 
End Sub

Open in new window

0
 
LVL 8

Author Comment

by:arcross
ID: 24105490
im using the "=" for the formulas.

basically, what im doing is to loop the rows in a datatable and add store teh values in an array.
Then im adding the array of the values of the row in a range of cells.:

Sheet.range("A" & x.ToString, LastColumn & x.ToString).value = arrCell  
'Arrcell contains all the values from the row.

Then im adding an extra column with formula. TO calculate the values of two of those columns.
But it shows the rsult as 0. But if i change manually the cells i want to calculate from text to 'Convert to number' its fine.
So i need to convert the cells through code.
   
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 24105517
Hi,

I am not clear here.  Are you saying the code I posted above does not work for you (after adjusting the worksheet and range)?

Alan.
0
 
LVL 8

Author Comment

by:arcross
ID: 24105575
Hi Alan, yes it dows work! thanks!

It seems i was posting mine while you posted yours so i didnt see it!
0
 
LVL 12

Expert Comment

by:Alan3285
ID: 24105592
Like ships passing in the night!

Glad it worked,

Alan.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question