Excel 2003 - Use VBA to convert "text" numbers to real Numbers 3

[TomCarnahan]

I access a number of external data sources, which unfortunately, download some numbers as text.

I would like to write a macro to look at a worksheet, and either globally, convert "text numbers" to real numbers, or to have it go through, ID the candidate and let me choose "yes" or "no" to make the conversion.

Has anyone seen any VBA examples that deal with 1) finding "text numbers" on a sheet, then, 2) converting them to real numbers?

Thank you ahead of time for your help!


--- Tom

 

[Loomah]

Hi
I don't know about finding text numbers but I use the follow (style of) code to convert selected cells. In my case this is usually enough as the numbers appear in columns and if one of them is thext, they all are.

dim c as range
for each c in selection
c=trim(c)
next

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

mornin' Loomah - would be a bit slow on large spreadsheets that

faster is

with Range("Blank Cell")
  .value = 1
  .copy
end with
Range("PasteRange").pastespecial paste:=xlvalues operation:=xlmultiply

To find these text numbers, you can use something like:

Application.ErrorCheckingOptions.NumberAsText = True
For each c in sheets("Sheet1").cells
  If c.Errors.Item(xlEmptyCellReferences).Value = True Then
     msgbox "Number Stored As Text"
  else
  end if
next

I would suggest that your best bet is to actually multiply all CONSTANT cells by 1 without checking 1st as it is only formulae that will get disrupted by the paste special action

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Loomah]

It is indeed VERY ssssslllllllooooooooooo

http://wwwwwwww

at times but for my purposes it kills two birds with a brick!

I actually use a little tool to clean data in a choice of ways depending on what's wrong with it. Unfortunately, to date, I've been unable to come up with something that will make rubbish data good!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

LOL - I been looking for that utility for years - that and the mind reading application that just builds whatever it is you are thinking about !

made a typo btw:

Application.ErrorCheckingOptions.NumberAsText = True
For each c in sheets("Sheet1").cells
  If c.Errors.Item(xlNumberAsText).Value = True Then
     msgbox "Number Stored As Text"
  else
  end if
next

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Checking all cells? Why not shorten it?

For the selection..

Sub ForceNumbers()
    Dim c As Range
    On Error Resume Next
    For Each c In Selection.SpecialCells(xlCellTypeConstants, 2)
        If IsNumeric(CDbl(c.Value)) Then
            c.Value = CDbl(c.Value)
        End If
    Next c
End Sub

or for the whole [active] sheet..

Sub ForceNumbers2()
    Dim c As Range, LastCol As Long, LastRow As Long
    On Error Resume Next
    LastCol = Cells.Find("*", after:=Cells(1, 1), searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
    LastRow = Cells.Find("*", after:=Cells(1, 1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    For Each c In Range(Cells(1, 1), Cells(LastRow, LastCol)).SpecialCells(xlCellTypeConstants, 2)
        If IsNumeric(CDbl(c.Value)) Then
            c.Value = CDbl(c.Value)
        End If
    Next c
End Sub

-----------
Regards,
Zack Barresse

 

[PHV]

Zack, why this :
If IsNumeric(CDbl(c.Value)) Then
Instead of this ?
If IsNumeric(c.Value) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bong]

why not just use the "val" function?

_________________
Bob Rashkin

 

[firefytr]

PHV, yeah, just habit I think.

        If IsNumeric(c.Value) Then
            c.Value = Val(c.Value)

That probably would be a better setup. I tend to use CDbl personally as most numbers can default to it. I forgot about Val using Strings and converting to Double anyway, same thing basically. My main issue is the loop though, that's generally the killer.

-----------
Regards,
Zack Barresse

 

[PHV]

The advantage of this:
If IsNumeric(c.Value) Then c.Value = Val(c.Value)

is that you don't raise conversion error anymore ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

Yeah, I did leave out an error handler. A little too much coffee this morning, eh? hehe

-----------
Regards,
Zack Barresse

 

[AnotherHiggins]

Unless I'm missing something, there is no need to loop at all.

To globally change all numeric strings to actual numbers you can just use something like

range("A:C").value = range("A:C").value

Any text will not be affected because the value of a text string is the text string itself. Anything that is already a number will also remain unchanged. Any numeric string such as '0123 should be converted to 123 (an actual number).

Just as a speed test, I just checked that code against 3 columns X 65000 rows - the code took less than one second to run.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Tom,

Your stated, "...or to have it go through, ID the candidate and let me choose "yes" or "no" to make the conversion."

This leads me to ask, do you want to convert ALL the values in a specified column to numbers or only SOME of the values in a specified column?


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[firefytr]

John, what about formulas that are not wanted to be set as their static value(s)?

-----------
Regards,
Zack Barresse

 

[AnotherHiggins]

Zack,

I access a number of external data sources, which unfortunately, download some numbers as text.

I just assumed that the imported data wouldn't contain formulae. I guess I could be wrong on that.

And to address Skip's point:

I would like to write a macro to look at a worksheet, and either globally, convert "text numbers" to real numbers, or to have it go through, ID the candidate and let me choose "yes" or "no" to make the conversion.

Tom, there is a big difference between have all values changed globally and having the choice on each one whether you want it changed. If global changes are ok (which, by my reading of the above quote, it seems to be) then that will be much quicker to just let happen.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[TomCarnahan]

Wow! ... my email was down and I just got back up. I am impressed by all the responses. I will have to go try all these out.

To give a little more info, I am working with an Excel add-in (API/DLL) that serves as an interface to query Hyperion/Essbase. Since all the analysts I support live and breath Excel, I have to use worksheets to get the data OR they have already pulled the data via this add-in and I get stuck making things look pretty (annoying cosmetics!).

Since I am relatively new to this add-in, I don't know all the "in"s and "out"s. There may be a way to make the data come out consistently numeric or consistently numbers as text. Unfortunately, it is always mixed. For appearance purposes, the text numbers left align and the real numbers right align.

I typically get all the data on one sheet (less than 65,000+ records), but sometimes, I get someone else's Excel file and the data dump is on multiple sheets with other worksheets that have formulas, so I wouldn't want the macro to work on more than one sheet and only on the ones I select.

When I try to manually convert them, nothing seems to convert them other then individually clicking on the "smart tags" and using that option to convert. Unfortunately, that is one cell at a time. The best that Excel help could offer is to type the correct number in an unused cell, copy it, hold down CTRL, click on all the cells that are text numbers, then paste (almost as time consuming).

Strangely enough, in my case, most of the time, the "text numbers" are the number ZERO.

As far as performance goes, I am willing to go take a break if I knew that when I came back, the text numbers were real numbers ... (the "weenie" way out), but that is because I have nothing better at the time.

I will try all of the things you suggested and let you know and dish out the appropriate "stars".

Many thanks to all!





--- Tom

 

[macropod]

Hi Tom,

Downloaded numeric data often arives as text because it either:
a) has a space character (ASCII 32 or ASCII 160) in the field; or
b) has a preceding tick mark (').

The following macro will take care of both. To run it on a specific range, just highlight that range. Otherwise, run it to clean up the whole worksheet. Formuale are not affetced. Cleans 'unwanted' leading & trailing spaces from cells containing text too.

Option Explicit
Dim SBar As Boolean

Private Sub MacroEntry()
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
End Sub

Sub TrimRange()
Call MacroEntry
On Error Resume Next
Dim Cell As Range
Dim CellCount As Long
Dim Percent As Integer
Dim i As Long
i = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
    CellCount = Selection.Rows.Count * Selection.Columns.Count
Else
    CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
    Cell.Replace What:=Chr(160), Replacement:=Chr(32)
    Cell.Value = Application.Trim(Cell.Value)
    i = i + 1
    If Int(i / CellCount * 100 + 0.5) = Percent + 1 Then
        Percent = Percent + 1
        Application.StatusBar = Percent & "% Trimmed"
    End If
Next Cell
MsgBox "Finished  trimming " & CellCount & " cells.", 64
Call MacroExit
End Sub

The reason there's so much code is that it's flexible, gives feedback on the status bar as to its progress (useful on really large worksheets like your's) and temporarily turns auto-recalc off to speed up processing.

Cheers

[MS MVP - Word]

 

[TomCarnahan]

Looks great! Have a star!

--- Tom