Solved

Convert cells in excel to text to number

Posted on 2009-04-09
8
349 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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