Converting Text Values To Values

[ackalbo]

Hello guys & gals

Below is an example of the report I have generated by our biometric system:

Excel 2010

	A	B	C	D	E
Dept Manager: MY Total Number of Log: 58
xxx	9/1/2013 2:53:44 PM	9/2/2013 12:04:41 AM
zzz	9/1/2013 2:53:22 PM	9/1/2013 6:13:38 PM
zzz	9/2/2013 3:40:20 PM	No Log Out
zzz	9/3/2013 3:25:36 PM	9/3/2013 6:27:28 PM
zzz	9/5/2013 3:05:08 PM	No Log Out
zzz	9/6/2013 3:39:57 PM	No Log Out
zzz	9/7/2013 3:59:26 PM	No Log Out
aaa	9/2/2013 2:53:34 PM	No Log Out
aaa	9/3/2013 3:26:27 PM	No Log Out
aaa	9/4/2013 3:00:42 PM	No Log Out
aaa	9/5/2013 3:03:58 PM	No Log Out
aaa	9/6/2013 2:41:42 PM	9/7/2013 12:02:56 AM
aaa	9/7/2013 3:35:38 PM	No Log Out

<tbody>
[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FFFF00"]Name of Employee[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #FFFF00"]Log - In[/TD]
[TD="bgcolor: #FFFF00"]Log - Out[/TD]
[TD="bgcolor: #FFFF00"]Total Hrs[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]9.18[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3.33[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]

[TD="align: right"]3.03[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]

[TD="align: right"]9.35[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]

[TD="align: right"]0[/TD]

</tbody>

Sheet1​

My concern is this, all the entries in column C and D are proceeded by an apostrophe and I need to remove the apostrophe from each and every one in order to use the entries in a meaningful way. In total I have, on average, approx 850 entries to change every week. I have had a look at ASAP Utilities and I could find an option to insert the apostrophe but not to remove one.

Can anyone suggest anything please?

Many thanks

 

[Redwolfx]

Try using Find/Replace?

 

[ackalbo]

Thx Redwolfx but I can't seem to find a way for Find/Replace to work without having to individually type each one...

 

[Weazel]

maybe try selecting column C then data...text to columns ...finish or

try putting the number 1 in a cell, copy it, select column C, paste special values and check multiply

 

[Gary's Student]

Try this small macro:

Sub durall()
    For Each r In ActiveSheet.UsedRange
        If r.PrefixCharacter = "'" Then
            r.Value = r.Value
        End If
    Next r
End Sub

 

[ackalbo]

Thank you very much for your ideas everyone

Gary's Student - Your macro worked like a charm - Thank you very much. Now, for my own personal benefit as I've just started to learn VBA, I shall dissect your code and try to understand it