macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

greg - 23 May 2006 12:24 GMT

Hi
how can I convert a field that has an ' before the number, for example
'4578 i would like to convert it to 4578
Greg

Reply to this Message

Norman Jones - 23 May 2006 12:37 GMT

Hi Greg,

For a VBA solution, try:

'=============>>
Public Sub DeleteApostrophes()
Dim WB As Workbook
Dim SH As Worksheet
Dim rCell As Range

Set WB = Workbooks("YourBook.xls") '<<===== CHANGE
Set SH = WB.Sheets("Sheet1") '<<===== CHANGE
Set rng = SH.Range("A1:A20") '<<===== CHANGE

For Each rCell In rng
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell

End Sub
'<<=============

---
Regards,
Norman

> Hi
> how can I convert a field that has an ' before the number, for example
> '4578 i would like to convert it to 4578
> Greg

Reply to this Message

CLR - 23 May 2006 13:01 GMT

Quite Cool Norman.........and in a nifty format that's "reusable" for future
tasks as well just by changing the IF statement........a keeper here, thanks
for the post.

Vaya con Dios,
Chuck, CABGx3

> Hi Greg,
>
[quoted text clipped - 27 lines]
> > '4578 i would like to convert it to 4578
> > Greg

Reply to this Message

CLR - 23 May 2006 12:46 GMT

Try left-click on an empty unused cell, then left-click the Format Painter
icon in the upper toolbar, then left-click on the target cell(s)

Vaya con Dios,
Chuck, CABGx3

> Hi
> how can I convert a field that has an ' before the number, for example
> '4578 i would like to convert it to 4578
> Greg

Reply to this Message

Dave Peterson - 23 May 2006 12:55 GMT

I like this technique:

Select an empty cell
edit|Copy
select the range of offending cells
edit|Paste special|check Add

> Hi
> how can I convert a field that has an ' before the number, for example
> '4578 i would like to convert it to 4578
> Greg

Signature

Dave Peterson

Reply to this Message