Excel: Number stored as text 3

[fountain27]

I am working with a system that exports data into Excel. The numbers that are exported Excel stores as text, hence you can not perform any functions until the text is converted to a value.

I have been using the worksheet function value(), but it is sloppy and causes my code to run slow.

Is there a better way to convert the text to a value? I have tried using the copy paste special (value) command, but it did not work.

Thanks for the help

 

[ETID]

if a text value is in A1 the in B1 =A1*1
...fill down

 

[fountain27]

Thanks for the fast reply. I tried that earlier, but since Excel does not view the item as a value * by 1 only returns a zero.

 

[ETID]

Paste this into a module...

Function force_num(in_cell) As Double
force_num = CDbl(in_cell)
End Function




usage:

=force_num(A1)

 

[ETID]

Oh ...and be sure to check the formatting of the cells ...reset them to general

 

[fountain27]

Thanks for information. It appears this custom function would work like the value(cell) function built into excel. I was hoping for a copy paste method, but I guess it can not be done. Things can never be too easy

I really appreciate you taking the time to reply to my post. Take care

 

[SkipVought]

Hi,
Select the column containing data. then run this procedure

Sub MakeNumeric()
  With Selection
    Set rng = Range(Cells(.Row, .Column), Cells(Cells.Rows.Count, .Column).End(xlUp))
  End With
  For Each r In rng
    With r
      .Value = .Value
    End With
  Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SonOfEmidec1100]

Im not an 'Excel person' but I recall something about setting the NumberFormat property of the column something like

Worksheets("Sheet1").Columns("MyColumn").NumberFormat = "0.00"



Maybe this is of use.

 

[xlbo]

Nope - numberformat will do nothing and I really don't see the need for any looping or incrementing - all you need to do is use the "Times 1" fix via code:

with activesheet
  lRow = .cells(65536,1).end(xlup).row
  .cells(lRow + 1,"A").value = 1
  .cells(lRow + 1,"A").copy
  .Range("A2:A" lRow).PasteSpecial Paste:=xlPasteValues, [b]Operation:=xlPasteSpecialOperationMultiply[/b]
  .cells(lRow + 1,"A").clearcontents
end with

This will convert a list of "textual numbers" in column A (starting in row 2) to true numbers and should be by some way the quickest way to achieve this

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[terzaghi]

Simple way:

do this:
1. export data (now column A1 contains the numbers in text format)
2. in B column =value(A1) etc. now column B contains numbers
3. paste special format and value from B to A. You converted text in A and you can delete column B.

hope this helps.

 

[fountain27]

Thanks to all for your time.

Skip,
I ended up using your code, it works much faster than my old worksheet function value().

 

[xlbo]

fountain - if you have more than a coupla hundred rows, you will find out that looping is FAR slower than using the PasteSpecial function......your call but you should try it out

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[fountain27]

Geoff,
Thanks that is extremely fast.