Remove apostrophe

[Adrae]

Is there a away to write a macro that will remove the apostrophe in all selected cells. I currently have dates with apostrophes in front so that they will be seen as text. I need them as dates now and find replace doesn't seem to be working.

Any ideas? Thanks

[Aladin Akyurek]

On 2002-03-26 08:33, Adrae wrote:
Is there a away to write a macro that will remove the apostrophe in all selected cells. I currently have dates with apostrophes in front so that they will be seen as text. I need them as dates now and find replace doesn't seem to be working.

Any ideas? Thanks

The option Data|Text to Columns should work when it is given ' as delimiter.

[IML]

On 2002-03-26 08:33, Adrae wrote:
Any ideas? Thanks

One idea- could you use =datevalue(), format it to your liking and copy it down? This may require copy, paste special values and the deletion of your original column.

I like this edit feature - it allows me to say to use the first (and best) idea from Aladin. They need an icon for a guy slapping himself on the forehead here!!!

[ This Message was edited by: IML on 2002-03-26 08:47 ]

[manney]

Why not just convert to date using Text To Columns (Data menu)?

[Cosmos75]

Try this.

Sub Remove()

Range("B1:B10").Select
Application.CutCopyMode = False
Cells.Replace What:="'", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
Range("A1").Select
End Sub

Edit # 1: Sorry, didn't read it through, I thought you were removing 's from text

[ This Message was edited by: Cosmos75 on 2002-03-26 09:56 ]

Edit # 2:

Sub Macro2()
Range("B1:B10").Select
Selection.TextToColumns
Destination:=Range("B1:B10"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="'", FieldInfo:=Array(1, 3)
End Sub

[ This Message was edited by: Cosmos75 on 2002-03-26 09:59 ]

[Henry]

What about Find-Replace

[Exactement]

Have a look at articles Q124739 and Q124935 in the MS Knowledge Base.

[Dave Hawley]

Hi Andrea

Just use:

Sub DoIt()
Selection = Selection.Value
End Sub

[Yogi Anand]

On 2002-03-26 08:33, Adrae wrote:
Is there a away to write a macro that will remove the apostrophe in all selected cells. I currently have dates with apostrophes in front so that they will be seen as text. I need them as dates now and find replace doesn't seem to be working.

Any ideas? Thanks

Hi Adrae:
How about multiplying the number with the ' as the beginning character by 1 into the adjacent cell. For example if '3333 is in B17, then C17=B17*1 and then format the number in C17 as Date ...
B17 C17
'33333 33333 -->formatted as Date 4/5/91


[ This Message was edited by: Yogi Anand on 2002-03-26 20:01 ]

[Tommy Bak]

Hi
Just a follow up on Yogi's answer.
Put 1 in random cell.
Choose copy
Mark your Date-cells
Choose paste special
Choose Multiply
All of your dates are now multiplied by one and converted to values.
Format them to dates

regards Tommy