Hi everybody,

I'm facing a simple , but annoying problem...which is driving me crazy...

154000 lines of information where extracted from a corporative database by a
third party for me. These lines
are in 16 .xls, distributed in 640 Sheets. The export process almost workly
perfectly, except for the fact that it showed up the classical 'Convert text
to number' problem. I tried some tricks to automate the corretion of this
problem, to try to turn everything in number, but no sucess.

It seems the only way is to to do manually that multiply by one excel hint...

But it is a lot of handwork to do that in all thesse sheets and files....
The Sheets have some columns with number and other with text...

SOmeone have any idea?

(I already now how to cycle automatically betwen all files and sheets...just
need the piece of code to solve the number issue)


Thanks in advance,
Cleber

How do I programmatically get numbers recognized as numbers in a excel
sheet? It contains some sort of data dump which is not recognized in excel
as a number. I select the column and then Format, Cells, Number. Then I
delete the last number in each cell and replace the same number. Then it is
recognized as a number and shifts to align to the right. Is there an easier
way?

Thank you in advance,

David

You can use the TextToColumn method under the Data Menu Bar, or you can use
the PasteSpecial method by copy a cell with a numeric value of 0, then paste
to all other cells as Value and Add Operation. Either trick does just fine.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000


Maybe the data is coming in as text.
Simply formatting the data to number will not do the trick, as you have
found.

Format all to General.

Copy an empty cell.

Select the range of data and Edit>Paste Special>Add>OK>Esc

If this doesn't work, perhaps you have some spaces or extra hidden
characters
from the data dump.

Post back if that's the case.


Gord Dibben MS Excel MVP

"Cleber Inacio" <(E-Mail Removed)> wrote in message
news:5010E7D9-9941-47E8-9CF8-(E-Mail Removed)...
> Hi everybody,
>
> I'm facing a simple , but annoying problem...which is driving me crazy...
>
> 154000 lines of information where extracted from a corporative database by
> a
> third party for me. These lines
> are in 16 .xls, distributed in 640 Sheets. The export process almost
> workly
> perfectly, except for the fact that it showed up the classical 'Convert
> text
> to number' problem. I tried some tricks to automate the corretion of this
> problem, to try to turn everything in number, but no sucess.
>
> It seems the only way is to to do manually that multiply by one excel
> hint...
>
> But it is a lot of handwork to do that in all thesse sheets and files....
> The Sheets have some columns with number and other with text...
>
> SOmeone have any idea?
>
> (I already now how to cycle automatically betwen all files and
> sheets...just
> need the piece of code to solve the number issue)
>
>
> Thanks in advance,
> Cleber

Here is our approach:

1. scan thru each cell in each worksheet
2. find cell that are formatted as Text, but which actually contain numbers
3. fix the cell

Sub numerify()
Dim r As Range
Count = 0
For Each w In Worksheets
w.Activate
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
r.Value = 1# * r.Value
r.NumberFormat = "General"
Count = Count + 1
End If
End If
Next
Next
MsgBox (Count & " cells changed")
End Sub


Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the macro from Excel:

1. ALT-F8
2. Select the macro
3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
Gary''s Student - gsnu200757


"Cleber Inacio" wrote:

> Hi everybody,
>
> I'm facing a simple , but annoying problem...which is driving me crazy...
>
> 154000 lines of information where extracted from a corporative database by a
> third party for me. These lines
> are in 16 .xls, distributed in 640 Sheets. The export process almost workly
> perfectly, except for the fact that it showed up the classical 'Convert text
> to number' problem. I tried some tricks to automate the corretion of this
> problem, to try to turn everything in number, but no sucess.
>
> It seems the only way is to to do manually that multiply by one excel hint...
>
> But it is a lot of handwork to do that in all thesse sheets and files....
> The Sheets have some columns with number and other with text...
>
> SOmeone have any idea?
>
> (I already now how to cycle automatically betwen all files and sheets...just
> need the piece of code to solve the number issue)
>
>
> Thanks in advance,
> Cleber

Gary,
thanks for you help...your routine rocks!
actually i was trying something similar, except for the IsNumeric function,
I couldn't find it on my help searches, so i got stuck on the detecting
potential
numbers.

I ran your version but it didint fix numbers, i also had tried PasteSpecial
method
and i got no success, even if doing that manually it worked but no way by
code.

But these are things that i dont even want to understand...now it works and
its
enough for me.

Thanks Again for the help Gary!! and keep helping ppl

Cleber

**Final Code:

Sub KillerNumerify()
Dim double_meu As Double
Dim r As Range
Count = 0

Application.ScreenUpdating = False
For Each w In Worksheets
w.Activate
'In Brazil we use comma instead of dot
Cells.Select
Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
For Each r In ActiveSheet.UsedRange
If Application.IsText(r.Value) Then
If IsNumeric(r.Value) Then
double_meu = CDbl(r.Value)
r.Clear ' my string cells are very powerful...need to kill them
before fixing
r.Value = double_meu
Count = Count + 1
End If
End If
Next
Next
MsgBox (Count & " cells changed")
Application.ScreenUpdating = True
End Sub

"Gary''s Student" wrote:

> Here is our approach:
>
> 1. scan thru each cell in each worksheet
> 2. find cell that are formatted as Text, but which actually contain numbers
> 3. fix the cell
>
> Sub numerify()
> Dim r As Range
> Count = 0
> For Each w In Worksheets
> w.Activate
> For Each r In ActiveSheet.UsedRange
> If Application.IsText(r.Value) Then
> If IsNumeric(r.Value) Then
> r.Value = 1# * r.Value
> r.NumberFormat = "General"
> Count = Count + 1
> End If
> End If
> Next
> Next
> MsgBox (Count & " cells changed")
> End Sub
>
>
> Macros are very easy to install and use:
>
> 1. ALT-F11 brings up the VBE window
> 2. ALT-I
> ALT-M opens a fresh module
> 3. paste the stuff in and close the VBE window
>
> If you save the workbook, the macro will be saved with it.
>
> To remove the macro:
>
> 1. bring up the VBE window as above
> 2. clear the code out
> 3. close the VBE window
>
> To use the macro from Excel:
>
> 1. ALT-F8
> 2. Select the macro
> 3. Touch RUN
>
> To learn more about macros in general, see:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> --
> Gary''s Student - gsnu200757
>
>
> "Cleber Inacio" wrote:
>
> > Hi everybody,
> >
> > I'm facing a simple , but annoying problem...which is driving me crazy...
> >
> > 154000 lines of information where extracted from a corporative database by a
> > third party for me. These lines
> > are in 16 .xls, distributed in 640 Sheets. The export process almost workly
> > perfectly, except for the fact that it showed up the classical 'Convert text
> > to number' problem. I tried some tricks to automate the corretion of this
> > problem, to try to turn everything in number, but no sucess.
> >
> > It seems the only way is to to do manually that multiply by one excel hint...
> >
> > But it is a lot of handwork to do that in all thesse sheets and files....
> > The Sheets have some columns with number and other with text...
> >
> > SOmeone have any idea?
> >
> > (I already now how to cycle automatically betwen all files and sheets...just
> > need the piece of code to solve the number issue)
> >
> >
> > Thanks in advance,
> > Cleber

While I'm thinking it may never arise in the situation the OP described, and
so I'm not sure it really needs to be addressed or not, your statement...

If Application.IsText(r.Value) Then

appears to not evaluate as True a cell that contains a number where the cell
was subsequently re-formatted as Text; hence, such "numbers" are not
converted and remain as Text.

I don't have a real feel for what the speed difference, if any, might be;
but I thought the following routine using TextToColumn might be quicker as
it only iterates through whole columns of data rather than through all of
the cells individually (and, at worst, it offers an alternate approach for
the readers of this thread to consider).

' The following subroutine assumes the text contains no
' Tab characters; if it does, a modification for the type
' of delimiter will be necessary
Sub ConvertTextNumbersToPureNumbers()
Dim X As Long
Dim LastCol As Long
Dim WS As Worksheet
Application.ScreenUpdating = False
On Error GoTo Whoops
For Each WS In Worksheets
LastCol = WS.UsedRange.Columns.Count
For X = 1 To LastCol
WS.Cells(1, X).EntireColumn.NumberFormat = "General"
WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1, X),
_
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
_
Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Next
Next
Whoops:
If Err.Number = 1004 Then
Err.Clear
Resume Next
End If
Application.ScreenUpdating = True
End Sub


Rick





"Gary''s Student" <(E-Mail Removed)> wrote in message
news:C12B3E19-3D3B-43E7-9B0E-(E-Mail Removed)...
> Here is our approach:
>
> 1. scan thru each cell in each worksheet
> 2. find cell that are formatted as Text, but which actually contain
> numbers
> 3. fix the cell
>
> Sub numerify()
> Dim r As Range
> Count = 0
> For Each w In Worksheets
> w.Activate
> For Each r In ActiveSheet.UsedRange
> If Application.IsText(r.Value) Then
> If IsNumeric(r.Value) Then
> r.Value = 1# * r.Value
> r.NumberFormat = "General"
> Count = Count + 1
> End If
> End If
> Next
> Next
> MsgBox (Count & " cells changed")
> End Sub
>
>
> Macros are very easy to install and use:
>
> 1. ALT-F11 brings up the VBE window
> 2. ALT-I
> ALT-M opens a fresh module
> 3. paste the stuff in and close the VBE window
>
> If you save the workbook, the macro will be saved with it.
>
> To remove the macro:
>
> 1. bring up the VBE window as above
> 2. clear the code out
> 3. close the VBE window
>
> To use the macro from Excel:
>
> 1. ALT-F8
> 2. Select the macro
> 3. Touch RUN
>
> To learn more about macros in general, see:
>
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> --
> Gary''s Student - gsnu200757
>
>
> "Cleber Inacio" wrote:
>
>> Hi everybody,
>>
>> I'm facing a simple , but annoying problem...which is driving me crazy...
>>
>> 154000 lines of information where extracted from a corporative database
>> by a
>> third party for me. These lines
>> are in 16 .xls, distributed in 640 Sheets. The export process almost
>> workly
>> perfectly, except for the fact that it showed up the classical 'Convert
>> text
>> to number' problem. I tried some tricks to automate the corretion of this
>> problem, to try to turn everything in number, but no sucess.
>>
>> It seems the only way is to to do manually that multiply by one excel
>> hint...
>>
>> But it is a lot of handwork to do that in all thesse sheets and files....
>> The Sheets have some columns with number and other with text...
>>
>> SOmeone have any idea?
>>
>> (I already now how to cycle automatically betwen all files and
>> sheets...just
>> need the piece of code to solve the number issue)
>>
>>
>> Thanks in advance,
>> Cleber

Rick,

In sheets with data, UsedRange doesn't necessarily begin in column A. It
begins wherever the first cell of data is.

In an new sheet, if I add data into columns E and F, then run your macro:

> LastCol = WS.UsedRange.Columns.Count
> For X = 1 To LastCol
> WS.Cells(1, X).EntireColumn.NumberFormat = "General"

It will modify columns A and B.

This modification will fix that:

WS.UsedRange.Cells(1, x).EntireColumn...


--
Tim Zych
SF, CA


"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:%(E-Mail Removed)...
> While I'm thinking it may never arise in the situation the OP described,
> and so I'm not sure it really needs to be addressed or not, your
> statement...
>
> If Application.IsText(r.Value) Then
>
> appears to not evaluate as True a cell that contains a number where the
> cell was subsequently re-formatted as Text; hence, such "numbers" are not
> converted and remain as Text.
>
> I don't have a real feel for what the speed difference, if any, might be;
> but I thought the following routine using TextToColumn might be quicker as
> it only iterates through whole columns of data rather than through all of
> the cells individually (and, at worst, it offers an alternate approach for
> the readers of this thread to consider).
>
> ' The following subroutine assumes the text contains no
> ' Tab characters; if it does, a modification for the type
> ' of delimiter will be necessary
> Sub ConvertTextNumbersToPureNumbers()
> Dim X As Long
> Dim LastCol As Long
> Dim WS As Worksheet
> Application.ScreenUpdating = False
> On Error GoTo Whoops
> For Each WS In Worksheets
> LastCol = WS.UsedRange.Columns.Count
> For X = 1 To LastCol
> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
> WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1,
> X), _
> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
> _
> Comma:=False, Space:=False, Other:=False, _
> FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
> Next
> Next
> Whoops:
> If Err.Number = 1004 Then
> Err.Clear
> Resume Next
> End If
> Application.ScreenUpdating = True
> End Sub
>
>
> Rick
>
>
>
>
>
> "Gary''s Student" <(E-Mail Removed)> wrote in
> message news:C12B3E19-3D3B-43E7-9B0E-(E-Mail Removed)...
>> Here is our approach:
>>
>> 1. scan thru each cell in each worksheet
>> 2. find cell that are formatted as Text, but which actually contain
>> numbers
>> 3. fix the cell
>>
>> Sub numerify()
>> Dim r As Range
>> Count = 0
>> For Each w In Worksheets
>> w.Activate
>> For Each r In ActiveSheet.UsedRange
>> If Application.IsText(r.Value) Then
>> If IsNumeric(r.Value) Then
>> r.Value = 1# * r.Value
>> r.NumberFormat = "General"
>> Count = Count + 1
>> End If
>> End If
>> Next
>> Next
>> MsgBox (Count & " cells changed")
>> End Sub
>>
>>
>> Macros are very easy to install and use:
>>
>> 1. ALT-F11 brings up the VBE window
>> 2. ALT-I
>> ALT-M opens a fresh module
>> 3. paste the stuff in and close the VBE window
>>
>> If you save the workbook, the macro will be saved with it.
>>
>> To remove the macro:
>>
>> 1. bring up the VBE window as above
>> 2. clear the code out
>> 3. close the VBE window
>>
>> To use the macro from Excel:
>>
>> 1. ALT-F8
>> 2. Select the macro
>> 3. Touch RUN
>>
>> To learn more about macros in general, see:
>>
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>>
>> --
>> Gary''s Student - gsnu200757
>>
>>
>> "Cleber Inacio" wrote:
>>
>>> Hi everybody,
>>>
>>> I'm facing a simple , but annoying problem...which is driving me
>>> crazy...
>>>
>>> 154000 lines of information where extracted from a corporative database
>>> by a
>>> third party for me. These lines
>>> are in 16 .xls, distributed in 640 Sheets. The export process almost
>>> workly
>>> perfectly, except for the fact that it showed up the classical 'Convert
>>> text
>>> to number' problem. I tried some tricks to automate the corretion of
>>> this
>>> problem, to try to turn everything in number, but no sucess.
>>>
>>> It seems the only way is to to do manually that multiply by one excel
>>> hint...
>>>
>>> But it is a lot of handwork to do that in all thesse sheets and
>>> files....
>>> The Sheets have some columns with number and other with text...
>>>
>>> SOmeone have any idea?
>>>
>>> (I already now how to cycle automatically betwen all files and
>>> sheets...just
>>> need the piece of code to solve the number issue)
>>>
>>>
>>> Thanks in advance,
>>> Cleber
>

Your comment is valid. Rather than testing IsText, I should test
NumberFormat or PrefixCharacter or both.

Thanks
--
Gary''s Student - gsnu200757


"Rick Rothstein (MVP - VB)" wrote:

> While I'm thinking it may never arise in the situation the OP described, and
> so I'm not sure it really needs to be addressed or not, your statement...
>
> If Application.IsText(r.Value) Then
>
> appears to not evaluate as True a cell that contains a number where the cell
> was subsequently re-formatted as Text; hence, such "numbers" are not
> converted and remain as Text.
>
> I don't have a real feel for what the speed difference, if any, might be;
> but I thought the following routine using TextToColumn might be quicker as
> it only iterates through whole columns of data rather than through all of
> the cells individually (and, at worst, it offers an alternate approach for
> the readers of this thread to consider).
>
> ' The following subroutine assumes the text contains no
> ' Tab characters; if it does, a modification for the type
> ' of delimiter will be necessary
> Sub ConvertTextNumbersToPureNumbers()
> Dim X As Long
> Dim LastCol As Long
> Dim WS As Worksheet
> Application.ScreenUpdating = False
> On Error GoTo Whoops
> For Each WS In Worksheets
> LastCol = WS.UsedRange.Columns.Count
> For X = 1 To LastCol
> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
> WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1, X),
> _
> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
> _
> Comma:=False, Space:=False, Other:=False, _
> FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
> Next
> Next
> Whoops:
> If Err.Number = 1004 Then
> Err.Clear
> Resume Next
> End If
> Application.ScreenUpdating = True
> End Sub
>
>
> Rick
>
>
>
>
>
> "Gary''s Student" <(E-Mail Removed)> wrote in message
> news:C12B3E19-3D3B-43E7-9B0E-(E-Mail Removed)...
> > Here is our approach:
> >
> > 1. scan thru each cell in each worksheet
> > 2. find cell that are formatted as Text, but which actually contain
> > numbers
> > 3. fix the cell
> >
> > Sub numerify()
> > Dim r As Range
> > Count = 0
> > For Each w In Worksheets
> > w.Activate
> > For Each r In ActiveSheet.UsedRange
> > If Application.IsText(r.Value) Then
> > If IsNumeric(r.Value) Then
> > r.Value = 1# * r.Value
> > r.NumberFormat = "General"
> > Count = Count + 1
> > End If
> > End If
> > Next
> > Next
> > MsgBox (Count & " cells changed")
> > End Sub
> >
> >
> > Macros are very easy to install and use:
> >
> > 1. ALT-F11 brings up the VBE window
> > 2. ALT-I
> > ALT-M opens a fresh module
> > 3. paste the stuff in and close the VBE window
> >
> > If you save the workbook, the macro will be saved with it.
> >
> > To remove the macro:
> >
> > 1. bring up the VBE window as above
> > 2. clear the code out
> > 3. close the VBE window
> >
> > To use the macro from Excel:
> >
> > 1. ALT-F8
> > 2. Select the macro
> > 3. Touch RUN
> >
> > To learn more about macros in general, see:
> >
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> > --
> > Gary''s Student - gsnu200757
> >
> >
> > "Cleber Inacio" wrote:
> >
> >> Hi everybody,
> >>
> >> I'm facing a simple , but annoying problem...which is driving me crazy...
> >>
> >> 154000 lines of information where extracted from a corporative database
> >> by a
> >> third party for me. These lines
> >> are in 16 .xls, distributed in 640 Sheets. The export process almost
> >> workly
> >> perfectly, except for the fact that it showed up the classical 'Convert
> >> text
> >> to number' problem. I tried some tricks to automate the corretion of this
> >> problem, to try to turn everything in number, but no sucess.
> >>
> >> It seems the only way is to to do manually that multiply by one excel
> >> hint...
> >>
> >> But it is a lot of handwork to do that in all thesse sheets and files....
> >> The Sheets have some columns with number and other with text...
> >>
> >> SOmeone have any idea?
> >>
> >> (I already now how to cycle automatically betwen all files and
> >> sheets...just
> >> need the piece of code to solve the number issue)
> >>
> >>
> >> Thanks in advance,
> >> Cleber
>
>

You raise a good point! However, I'm not sure I'm completely comfortable
with your proposed fix.... there will be just as many columns affected for
the NumberFormat'ting as for the TextToColumn'ing, but the columns being
acted on will be different. I could patch the TextToColumn line in the same
way you did the NumberFormat statement also, I guess, but I'm thinking just
changing the For-Next loop's start and stop values would be sufficient (and
keep the rest of my code as originally proposed)....

Sub ConvertTextNumbersToPureNumbers()
Dim X As Long
Dim StartCol As Long
Dim LastCol As Long
Dim WS As Worksheet
Application.ScreenUpdating = False
On Error GoTo Whoops
For Each WS In Worksheets
StartCol = WS.UsedRange.Column
LastCol = WS.UsedRange.Columns.Count
For X = StartCol To LastCol
WS.Cells(1, X).EntireColumn.NumberFormat = "General"
WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1, X),
_
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
_
Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Next
Next
Whoops:
If Err.Number = 1004 Then
Err.Clear
Resume Next
End If
Application.ScreenUpdating = True
End Sub


Rick


"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET> wrote in message
news:(E-Mail Removed)...
> Rick,
>
> In sheets with data, UsedRange doesn't necessarily begin in column A. It
> begins wherever the first cell of data is.
>
> In an new sheet, if I add data into columns E and F, then run your macro:
>
>> LastCol = WS.UsedRange.Columns.Count
>> For X = 1 To LastCol
>> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
>
> It will modify columns A and B.
>
> This modification will fix that:
>
> WS.UsedRange.Cells(1, x).EntireColumn...
>
>
> --
> Tim Zych
> SF, CA
>
>
> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
> message news:%(E-Mail Removed)...
>> While I'm thinking it may never arise in the situation the OP described,
>> and so I'm not sure it really needs to be addressed or not, your
>> statement...
>>
>> If Application.IsText(r.Value) Then
>>
>> appears to not evaluate as True a cell that contains a number where the
>> cell was subsequently re-formatted as Text; hence, such "numbers" are not
>> converted and remain as Text.
>>
>> I don't have a real feel for what the speed difference, if any, might be;
>> but I thought the following routine using TextToColumn might be quicker
>> as it only iterates through whole columns of data rather than through all
>> of the cells individually (and, at worst, it offers an alternate approach
>> for the readers of this thread to consider).
>>
>> ' The following subroutine assumes the text contains no
>> ' Tab characters; if it does, a modification for the type
>> ' of delimiter will be necessary
>> Sub ConvertTextNumbersToPureNumbers()
>> Dim X As Long
>> Dim LastCol As Long
>> Dim WS As Worksheet
>> Application.ScreenUpdating = False
>> On Error GoTo Whoops
>> For Each WS In Worksheets
>> LastCol = WS.UsedRange.Columns.Count
>> For X = 1 To LastCol
>> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
>> WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1,
>> X), _
>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
>> ConsecutiveDelimiter:=False, Tab:=True,
>> Semicolon:=False, _
>> Comma:=False, Space:=False, Other:=False, _
>> FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
>> Next
>> Next
>> Whoops:
>> If Err.Number = 1004 Then
>> Err.Clear
>> Resume Next
>> End If
>> Application.ScreenUpdating = True
>> End Sub
>>
>>
>> Rick
>>
>>
>>
>>
>>
>> "Gary''s Student" <(E-Mail Removed)> wrote in
>> message news:C12B3E19-3D3B-43E7-9B0E-(E-Mail Removed)...
>>> Here is our approach:
>>>
>>> 1. scan thru each cell in each worksheet
>>> 2. find cell that are formatted as Text, but which actually contain
>>> numbers
>>> 3. fix the cell
>>>
>>> Sub numerify()
>>> Dim r As Range
>>> Count = 0
>>> For Each w In Worksheets
>>> w.Activate
>>> For Each r In ActiveSheet.UsedRange
>>> If Application.IsText(r.Value) Then
>>> If IsNumeric(r.Value) Then
>>> r.Value = 1# * r.Value
>>> r.NumberFormat = "General"
>>> Count = Count + 1
>>> End If
>>> End If
>>> Next
>>> Next
>>> MsgBox (Count & " cells changed")
>>> End Sub
>>>
>>>
>>> Macros are very easy to install and use:
>>>
>>> 1. ALT-F11 brings up the VBE window
>>> 2. ALT-I
>>> ALT-M opens a fresh module
>>> 3. paste the stuff in and close the VBE window
>>>
>>> If you save the workbook, the macro will be saved with it.
>>>
>>> To remove the macro:
>>>
>>> 1. bring up the VBE window as above
>>> 2. clear the code out
>>> 3. close the VBE window
>>>
>>> To use the macro from Excel:
>>>
>>> 1. ALT-F8
>>> 2. Select the macro
>>> 3. Touch RUN
>>>
>>> To learn more about macros in general, see:
>>>
>>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>
>>>
>>> --
>>> Gary''s Student - gsnu200757
>>>
>>>
>>> "Cleber Inacio" wrote:
>>>
>>>> Hi everybody,
>>>>
>>>> I'm facing a simple , but annoying problem...which is driving me
>>>> crazy...
>>>>
>>>> 154000 lines of information where extracted from a corporative database
>>>> by a
>>>> third party for me. These lines
>>>> are in 16 .xls, distributed in 640 Sheets. The export process almost
>>>> workly
>>>> perfectly, except for the fact that it showed up the classical 'Convert
>>>> text
>>>> to number' problem. I tried some tricks to automate the corretion of
>>>> this
>>>> problem, to try to turn everything in number, but no sucess.
>>>>
>>>> It seems the only way is to to do manually that multiply by one excel
>>>> hint...
>>>>
>>>> But it is a lot of handwork to do that in all thesse sheets and
>>>> files....
>>>> The Sheets have some columns with number and other with text...
>>>>
>>>> SOmeone have any idea?
>>>>
>>>> (I already now how to cycle automatically betwen all files and
>>>> sheets...just
>>>> need the piece of code to solve the number issue)
>>>>
>>>>
>>>> Thanks in advance,
>>>> Cleber
>>
>
>

Rick,

My intention in tweaking the last macro was to show that the approach taken
would produce an undesireable side effect, and to offer a very specific fix,
not to create a robust macro. Now that I look at it more closely, it seems
like the column looping is unnecessary.

This would do the same thing as your macro.

Dim WS As Worksheet
For Each WS In Worksheets
WS.UsedRange.Columns(1).TextToColumns _
Destination:=WS.UsedRange.Columns(1).Cells(1, 1), _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
Comma:=True ' Tab:=True (swap out as needed)
Next

The notion you have of cycling through the columns to perform formatting
won't work with TextToColumns. Excel gives priority to FieldInfo to
determine the formatted output, overriding pre-formatted columns. Then data
coersion can still occur after the TextToColumns process, e,g. "Jan 1" to
1/1/2007.

By the way, this won't work unless UsedRange starts in column A

> StartCol = WS.UsedRange.Column
> LastCol = WS.UsedRange.Columns.Count

Should be:
LastCol = StartCol + WS.UsedRange.Columns.Count - 1

Not that I would go with that, just following up on my original point about
range offsets..

Tim
--
Tim Zych
SF, CA

"Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> You raise a good point! However, I'm not sure I'm completely comfortable
> with your proposed fix.... there will be just as many columns affected for
> the NumberFormat'ting as for the TextToColumn'ing, but the columns being
> acted on will be different. I could patch the TextToColumn line in the
> same way you did the NumberFormat statement also, I guess, but I'm
> thinking just changing the For-Next loop's start and stop values would be
> sufficient (and keep the rest of my code as originally proposed)....
>
> Sub ConvertTextNumbersToPureNumbers()
> Dim X As Long
> Dim StartCol As Long
> Dim LastCol As Long
> Dim WS As Worksheet
> Application.ScreenUpdating = False
> On Error GoTo Whoops
> For Each WS In Worksheets
> StartCol = WS.UsedRange.Column
> LastCol = WS.UsedRange.Columns.Count
> For X = StartCol To LastCol
> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
> WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1,
> X), _
> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
> ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
> _
> Comma:=False, Space:=False, Other:=False, _
> FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
> Next
> Next
> Whoops:
> If Err.Number = 1004 Then
> Err.Clear
> Resume Next
> End If
> Application.ScreenUpdating = True
> End Sub
>
>
> Rick
>
>
> "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET> wrote in message
> news:(E-Mail Removed)...
>> Rick,
>>
>> In sheets with data, UsedRange doesn't necessarily begin in column A. It
>> begins wherever the first cell of data is.
>>
>> In an new sheet, if I add data into columns E and F, then run your macro:
>>
>>> LastCol = WS.UsedRange.Columns.Count
>>> For X = 1 To LastCol
>>> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
>>
>> It will modify columns A and B.
>>
>> This modification will fix that:
>>
>> WS.UsedRange.Cells(1, x).EntireColumn...
>>
>>
>> --
>> Tim Zych
>> SF, CA
>>
>>
>> "Rick Rothstein (MVP - VB)" <(E-Mail Removed)> wrote in
>> message news:%(E-Mail Removed)...
>>> While I'm thinking it may never arise in the situation the OP described,
>>> and so I'm not sure it really needs to be addressed or not, your
>>> statement...
>>>
>>> If Application.IsText(r.Value) Then
>>>
>>> appears to not evaluate as True a cell that contains a number where the
>>> cell was subsequently re-formatted as Text; hence, such "numbers" are
>>> not converted and remain as Text.
>>>
>>> I don't have a real feel for what the speed difference, if any, might
>>> be; but I thought the following routine using TextToColumn might be
>>> quicker as it only iterates through whole columns of data rather than
>>> through all of the cells individually (and, at worst, it offers an
>>> alternate approach for the readers of this thread to consider).
>>>
>>> ' The following subroutine assumes the text contains no
>>> ' Tab characters; if it does, a modification for the type
>>> ' of delimiter will be necessary
>>> Sub ConvertTextNumbersToPureNumbers()
>>> Dim X As Long
>>> Dim LastCol As Long
>>> Dim WS As Worksheet
>>> Application.ScreenUpdating = False
>>> On Error GoTo Whoops
>>> For Each WS In Worksheets
>>> LastCol = WS.UsedRange.Columns.Count
>>> For X = 1 To LastCol
>>> WS.Cells(1, X).EntireColumn.NumberFormat = "General"
>>> WS.Cells(1, X).EntireColumn.TextToColumns Destination:=WS.Cells(1,
>>> X), _
>>> DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
>>> ConsecutiveDelimiter:=False, Tab:=True,
>>> Semicolon:=False, _
>>> Comma:=False, Space:=False, Other:=False, _
>>> FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
>>> Next
>>> Next
>>> Whoops:
>>> If Err.Number = 1004 Then
>>> Err.Clear
>>> Resume Next
>>> End If
>>> Application.ScreenUpdating = True
>>> End Sub
>>>
>>>
>>> Rick
>>>
>>>
>>>
>>>
>>>
>>> "Gary''s Student" <(E-Mail Removed)> wrote in
>>> message news:C12B3E19-3D3B-43E7-9B0E-(E-Mail Removed)...
>>>> Here is our approach:
>>>>
>>>> 1. scan thru each cell in each worksheet
>>>> 2. find cell that are formatted as Text, but which actually contain
>>>> numbers
>>>> 3. fix the cell
>>>>
>>>> Sub numerify()
>>>> Dim r As Range
>>>> Count = 0
>>>> For Each w In Worksheets
>>>> w.Activate
>>>> For Each r In ActiveSheet.UsedRange
>>>> If Application.IsText(r.Value) Then
>>>> If IsNumeric(r.Value) Then
>>>> r.Value = 1# * r.Value
>>>> r.NumberFormat = "General"
>>>> Count = Count + 1
>>>> End If
>>>> End If
>>>> Next
>>>> Next
>>>> MsgBox (Count & " cells changed")
>>>> End Sub
>>>>
>>>>
>>>> Macros are very easy to install and use:
>>>>
>>>> 1. ALT-F11 brings up the VBE window
>>>> 2. ALT-I
>>>> ALT-M opens a fresh module
>>>> 3. paste the stuff in and close the VBE window
>>>>
>>>> If you save the workbook, the macro will be saved with it.
>>>>
>>>> To remove the macro:
>>>>
>>>> 1. bring up the VBE window as above
>>>> 2. clear the code out
>>>> 3. close the VBE window
>>>>
>>>> To use the macro from Excel:
>>>>
>>>> 1. ALT-F8
>>>> 2. Select the macro
>>>> 3. Touch RUN
>>>>
>>>> To learn more about macros in general, see:
>>>>
>>>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>>>
>>>>
>>>> --
>>>> Gary''s Student - gsnu200757
>>>>
>>>>
>>>> "Cleber Inacio" wrote:
>>>>
>>>>> Hi everybody,
>>>>>
>>>>> I'm facing a simple , but annoying problem...which is driving me
>>>>> crazy...
>>>>>
>>>>> 154000 lines of information where extracted from a corporative
>>>>> database by a
>>>>> third party for me. These lines
>>>>> are in 16 .xls, distributed in 640 Sheets. The export process almost
>>>>> workly
>>>>> perfectly, except for the fact that it showed up the classical
>>>>> 'Convert text
>>>>> to number' problem. I tried some tricks to automate the corretion of
>>>>> this
>>>>> problem, to try to turn everything in number, but no sucess.
>>>>>
>>>>> It seems the only way is to to do manually that multiply by one excel
>>>>> hint...
>>>>>
>>>>> But it is a lot of handwork to do that in all thesse sheets and
>>>>> files....
>>>>> The Sheets have some columns with number and other with text...
>>>>>
>>>>> SOmeone have any idea?
>>>>>
>>>>> (I already now how to cycle automatically betwen all files and
>>>>> sheets...just
>>>>> need the piece of code to solve the number issue)
>>>>>
>>>>>
>>>>> Thanks in advance,
>>>>> Cleber
>>>
>>
>>
>

Possibly you have spaces in your data, or your data contains
non-breaking spaces in html that is &nbsp; and in VBA
it would be CHR(160).

TrimALL macro on my Rearranging Data in Columns page
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
HTH,
David McRitchie, Microsoft MVP -- Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm


"Cleber Inacio" <(E-Mail Removed)> wrote in message news:95F7095A-9251-48F1-BA92-(E-Mail Removed)...
> Gary,
> thanks for you help...your routine rocks!
> actually i was trying something similar, except for the IsNumeric function,
> I couldn't find it on my help searches, so i got stuck on the detecting
> potential
> numbers.
>
> I ran your version but it didint fix numbers, i also had tried PasteSpecial
> method
> and i got no success, even if doing that manually it worked but no way by
> code.
>
> But these are things that i dont even want to understand...now it works and
> its
> enough for me.
>
> Thanks Again for the help Gary!! and keep helping ppl
>
> Cleber
>
> **Final Code:
>
> Sub KillerNumerify()
> Dim double_meu As Double
> Dim r As Range
> Count = 0
>
> Application.ScreenUpdating = False
> For Each w In Worksheets
> w.Activate
> 'In Brazil we use comma instead of dot
> Cells.Select
> Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> For Each r In ActiveSheet.UsedRange
> If Application.IsText(r.Value) Then
> If IsNumeric(r.Value) Then
> double_meu = CDbl(r.Value)
> r.Clear ' my string cells are very powerful...need to kill them
> before fixing
> r.Value = double_meu
> Count = Count + 1
> End If
> End If
> Next
> Next
> MsgBox (Count & " cells changed")
> Application.ScreenUpdating = True
> End Sub
>
> "Gary''s Student" wrote:
>
>> Here is our approach:
>>
>> 1. scan thru each cell in each worksheet
>> 2. find cell that are formatted as Text, but which actually contain numbers
>> 3. fix the cell
>>
>> Sub numerify()
>> Dim r As Range
>> Count = 0
>> For Each w In Worksheets
>> w.Activate
>> For Each r In ActiveSheet.UsedRange
>> If Application.IsText(r.Value) Then
>> If IsNumeric(r.Value) Then
>> r.Value = 1# * r.Value
>> r.NumberFormat = "General"
>> Count = Count + 1
>> End If
>> End If
>> Next
>> Next
>> MsgBox (Count & " cells changed")
>> End Sub
>>
>>
>> Macros are very easy to install and use:
>>
>> 1. ALT-F11 brings up the VBE window
>> 2. ALT-I
>> ALT-M opens a fresh module
>> 3. paste the stuff in and close the VBE window
>>
>> If you save the workbook, the macro will be saved with it.
>>
>> To remove the macro:
>>
>> 1. bring up the VBE window as above
>> 2. clear the code out
>> 3. close the VBE window
>>
>> To use the macro from Excel:
>>
>> 1. ALT-F8
>> 2. Select the macro
>> 3. Touch RUN
>>
>> To learn more about macros in general, see:
>>
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>>
>> --
>> Gary''s Student - gsnu200757
>>
>>
>> "Cleber Inacio" wrote:
>>
>> > Hi everybody,
>> >
>> > I'm facing a simple , but annoying problem...which is driving me crazy...
>> >
>> > 154000 lines of information where extracted from a corporative database by a
>> > third party for me. These lines
>> > are in 16 .xls, distributed in 640 Sheets. The export process almost workly
>> > perfectly, except for the fact that it showed up the classical 'Convert text
>> > to number' problem. I tried some tricks to automate the corretion of this
>> > problem, to try to turn everything in number, but no sucess.
>> >
>> > It seems the only way is to to do manually that multiply by one excel hint...
>> >
>> > But it is a lot of handwork to do that in all thesse sheets and files....
>> > The Sheets have some columns with number and other with text...
>> >
>> > SOmeone have any idea?
>> >
>> > (I already now how to cycle automatically betwen all files and sheets...just
>> > need the piece of code to solve the number issue)
>> >
>> >
>> > Thanks in advance,
>> > Cleber