• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Convert cells in excel to text to number

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
arcross
Asked:
arcross
1 Solution
 
mfhorizonCommented:
while storing excel formulas from vb.net or any other language, use equal sign (=) before the formula then it must calculate automatically.
0
 
AlanConsultantCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
How do you export ? Can you show some code?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AlanConsultantCommented:
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
 
arcrossAuthor Commented:
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
 
AlanConsultantCommented:
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
 
arcrossAuthor Commented:
Hi Alan, yes it dows work! thanks!

It seems i was posting mine while you posted yours so i didnt see it!
0
 
AlanConsultantCommented:
Like ships passing in the night!

Glad it worked,

Alan.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now