Solved

Convert cells in excel to text to number

Posted on 2009-04-09
8
347 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

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…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

756 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