how to convert a range from numeric to text type? but quickly

[lauralucas]

hello

I'm currently using this macro for converting a column that has mixed
types (text and numeric) and change it to being text.

Sub AddSpace()

Dim r As Range
Dim Wks As Worksheet
Dim cell As Object
Dim found As Boolean

For IndexSheet = 1 To Sheets.Count
Set Wks = Sheets(IndexSheet)
With Wks
.Activate

'find the column that has to be changed
found = False
For Each r In .Range("1:1")
If r.Value2 = "Values" Or r.Value2 = "Value" Then
r.Activate
found = True
Exit For
End If
Next
If found = True Then

Range(Selection, Selection.End(xlDown)).Select 'this is the
'column to change


Selection.NumberFormat = "@" 'not there yet, we have to
'add and remove a space
'from each cell to achieve desired result

For Each cell In Selection 'this is the slow part,
'this is too slow for 39800 rows
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End If
End With

Next
ActiveWorkbook.Save
End Sub

 

[Tom Ogilvy]

Sub AddSpace()

Dim r As Range
Dim Wks As Worksheet
Dim cell As Object
Dim found As Boolean
Dim v as Variant

For IndexSheet = 1 To Sheets.Count
Set Wks = Sheets(IndexSheet)
With Wks
.Activate

'find the column that has to be changed
found = False
For Each r In .Range("1:1")
If r.Value2 = "Values" Or r.Value2 = "Value" Then
r.Activate
found = True
Exit For
End If
Next
If found = True Then

Range(Selection, Selection.End(xlDown)).Select 'this is the
'column to change


Selection.NumberFormat = "@" 'not there yet, we have to
'add and remove a space
'from each cell to achieve desired result
v = Selection
for i = 1 to ubound(v)
if isnumeric(v) then
v(i,1) = "'" & v(i,1)
end if
next i
Selection.Value = v
End If
End With

Next
ActiveWorkbook.Save
End Sub

 

[lauralucas]

sorry, that didn't work, in excel application, a green triangle should
appear with the error/warning "Number stored as text"
That is the only way to make sure the number is really of TEXT type,
and running your macro this warning didn't appear...

 

[Tom Ogilvy]

mea culpa, I had a typo:

Sub AddSpace()

Dim r As Range
Dim Wks As Worksheet
Dim cell As Object
Dim found As Boolean
Dim v as Variant

For IndexSheet = 1 To Sheets.Count
Set Wks = Sheets(IndexSheet)
With Wks
.Activate

'find the column that has to be changed
found = False
For Each r In .Range("1:1")
If r.Value2 = "Values" Or r.Value2 = "Value" Then
r.Activate
found = True
Exit For
End If
Next
If found = True Then

Range(Selection, Selection.End(xlDown)).Select 'this is the
'column to change


Selection.NumberFormat = "@" 'not there yet, we have to
'add and remove a space
'from each cell to achieve desired result
v = Selection
for i = 1 to ubound(v)
if isnumeric(v(i,1)) then
v(i,1) = "'" & v(i,1)
end if
next i
Selection.Value = v
End If
End With

Next
ActiveWorkbook.Save
End Sub

 

[lauralucas]

wow thanks a lot, that did it a lot faster =)

In case anyone needs this, this is for resolving this issue:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/
http://support.microsoft.com/default.aspx?scid=kb;en-us;815277

this didn't work for me:
http://support.microsoft.com/kb/194124/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;257819