PDA

View Full Version : [SOLVED] Remove Apostrophe from string



Oryos
12-30-2016, 06:18 AM
Hi everyone,

I want to automate a daily PnL reporting with help of a macro.
In order to work with the data I export an excel file from another system. I cannot change the export format.
Within this file I have two columns with dates that have an apostrophe at the beginning of the value string. Now this apostrophe is causing a lot of problem for me because I want to work with these values in date format.
I read a lot about it and it seems those apostrophe are a common issue. I thought removing the apostrophe with this code


Sub test()
Dim wb As Worksheet
Dim rngSource As Range
Dim rngTarget As Range
Dim vSource As Variant
Dim rCell As Range
Dim vItem As Variant
Dim sTemp As String
Dim dTemp As Date
Dim aTemp() As String

ThisWorkbook.Activate
Set wb = ThisWorkbook.Worksheets(1)

Set rngSource = Range(wb.Range("A1"), wb.Range("B1").End(xlDown))

For Each rCell In rngSource
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Text
End If
Next
End Sub

That didnt help.
Than i tried:



sTemp = CStr(rCell.Value)
If Left(sTemp, 1) = "'" Then
sTemp = Right(sTemp, Len(sTemp) - 1)
rCell.Value = sTemp
End If

But the apostrophe still remain. Any idea how I remove the apostrophe with help of VBA?

Thanks and happy new year to you all!

I've attached the file for your reference.
17925

mana
12-30-2016, 07:06 AM
I think you can use "Text to Columns"
You don't need to use VBA

JKwan
12-30-2016, 07:07 AM
try this:

Sub test()
Dim wb As Worksheet
Dim sTemp As Date
Dim lRow As Long
Dim lCol As Long
Set wb = ThisWorkbook.Worksheets(1)


For lRow = 2 To 554
For lCol = 1 To 2
sTemp = Cells(lRow, lCol)
Cells(lRow, lCol + 2) = Format(sTemp, "dd/mm/yyyy hh:mm:ss")
Next lCol
Next lRow
End Sub

mike7952
12-31-2016, 09:20 AM
This will work


Sub abc()
For Each cell In Range("a1").CurrentRegion
If IsDate(cell.Value) Then cell.Value = CDate(cell.Value)
Next
End Sub

Oryos
01-01-2017, 10:04 AM
Happy new year to you all!

thanks for your quick replies. I appreciate it.
@mana: I want to use VBA because I'd like to automate the PnL reporting as much as possible.

@JKwan and mike: Thanks for your solutions. I realized that you dont handle the apostrophe at all. So thats very interesting because i thought i have to get rid of the apostrophe first in order to work with the data but I dont.
thats great! Thanks for your help!