Solved

Convert cells in excel to text to number

Posted on 2009-04-09
8
341 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
Comment Utility
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
Comment Utility
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
Comment Utility
How do you export ? Can you show some code?
0
 
LVL 12

Accepted Solution

by:
Alan3285 earned 500 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 8

Author Comment

by:arcross
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Like ships passing in the night!

Glad it worked,

Alan.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sending Text to device in .NET 9 30
Setting location of a form 4 21
.net VBA word safemode 1 23
Round a string to two digits 12 23
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now