Remove ' From Fields

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
K

Karl Burrows

Hi!

Someone sent me an XP worksheet to import into another database.
Unfortunately, they put a hash ' in front of every item creating a text
field instead of what they should be. Edit, Replace doesn't work. Any
other ideas?

Thanks!
 
Hi
try the following
- select your data
- run the following macro ('borrowed' by Dave Peterson)
-do this on a backup copy of your data

Option Explicit
Sub remove_it()

Dim myCell As Range

For Each myCell In Selection.Cells
With myCell
If .PrefixCharacter = "'" Then
.NumberFormat = "General"
.Value = myCell.Value
End If
End With
Next myCell

End Sub
 
Hi!

I've found that multiplying the "text" number by 1 turns it into
number. Put a 1 in a convenient cell and copy it: select the data:
paste special with the multiply button activated.

Al
 
If that guy is using "with mycell", then maybe he should have used:

.value = .value
instead of
.value = mycell.value

(But you never know what that guy is doing!)
 
Here's what I actually used:

Sub removecells()

Dim myCell As Range

For Each myCell In Selection.Cells
With myCell
If .PrefixCharacter = "'" Then
.NumberFormat = "General"
.Value = myCell.Value
End If
End With
Next myCell

End Sub

I just highlighted the cells and ran it. Worked perfectly.

If that guy is using "with mycell", then maybe he should have used:

..value = .value
instead of
..value = mycell.value

(But you never know what that guy is doing!)
 
it worked ok, but this line:

..Value = myCell.Value

could be changed to
..Value = .Value

And it'll still work ok. (and looks much prettier!)
 
Excellent! I am just learning VBA, so any little bit helps!

it worked ok, but this line:

..Value = myCell.Value

could be changed to
..Value = .Value

And it'll still work ok. (and looks much prettier!)
 
Back
Top