Remove leading Quote mark from string e.g '1-4 to get only 1-4

[capson]

Hello,

This has turned out to be hard, I have searched and searched on how to do this but found nothing that works

I have data that comes from a survey app, in column V that there is data like this

Years of Service
1-4
5-9
10-14
15-20
20-24
.
.
.

all the strings for 1-4, 5-9, 10-14 are being read by Excell as Dates and being turned into numbers 42739, 42864, 43022

So I first with vba FindReplace

[{"42739", "42864", "43022"}] >>  [{"'42739", "'42864", "'43022"}]  'I add leading single qoute mark

Then I format the Column with vba

rng.NumberFormat = "@"

Then with vba FindReplace

[{"'42739", "'42864", "'43022"}]  >>  [{"1-4", "5-9", "10-14"}]

I need this final step in vba

[{"'1-4", "'5-9", "'10-14"}]   >>   [{"1-4", "5-9", "10-14"}]  'Remove the leading single quote mark

BUT I can find NOTHING that will do this

Thanks for any help on this

 

[steve the fish]

How is the data getting into excel? Format the column to text first.

 

[capson]

Steve, thank you for the response. It is coming from a CSV file, so the problem already exists by the time I get it.

Marcelo, thank you for the response. That reversed all the entries e.g. '1-4 became '4-1

 

[steve the fish]

See if this formula corrects everything. Its not easy for me to test as im using UK dates:

=IF(ISNUMBER(A1),IF(YEAR(A1)=YEAR(TODAY()),TEXT(A1,"m-d"),TEXT(A1,"m-yy")),A1)

Then we can convert to VBA if you require.

 

[capson]

Steve, thanks again.

The formula works if I add it manually, but I need a VBA solution, so I tried

Sub test()
    Dim LRow As Long
    Dim ws As Worksheet


    Set ws = Sheets("Z")


    With ws
    LRow = ws.Range("A" & .Rows.Count).End(xlUp).Row


       With .Range("K1:K" & LRow)
            .Formula = "=IF(ISNUMBER(A1),IF(YEAR(A1)=YEAR(TODAY()),TEXT(A1,""m-d""),TEXT(A1,""m-yy"")),A1)"
            .value = .value
       End With
    End With
End Sub

But this gives the same problem in that the cells give date again

Is there a way to use your formula in vba that will work?

Thank you

 

[Rick Rothstein]

Give this code a try...

Sub RemoveApostrophes()
  Dim Data As Variant
  With Range("K1", Cells(Rows.Count, "K").End(xlUp))
    Data = .Value
    .Clear
    .NumberFormat = "@"
    .Value = Data
  End With
End Sub

 

[capson]

Rick, that worked!! not sure why that is working though, thank you

 

[capson]

Rick,

I think I get it, you put the values into an Array then, cleared the range, formatted the range as "Text" then posted back to the range, and the ' was not copied to the array only the numerical value, is this correct?

 

[Rick Rothstein]

Rick, that worked!! not sure why that is working though, thank you

The code works by storing all the values in the cells in an VB array in memory (the values do not include the leading apostrophe because that is not part of the value in the cell, rather, it is a directive to Excel to treat what comes after it as text), completely clearing the cell of whatever is in it, formatted the cell as text so that new values remain as entered without Excel trying to interpret them), then placing all of the stored values back in the cells they came from.