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
5	Dept Manager: MY Total Number of Log: 58
6	Name of Employee		Log - In	Log - Out	Total Hrs
7	xxx		9/1/2013 2:53:44 PM	9/2/2013 12:04:41 AM	9.18
8	zzz		9/1/2013 2:53:22 PM	9/1/2013 6:13:38 PM	3.33
9	zzz		9/2/2013 3:40:20 PM	No Log Out	0
10	zzz		9/3/2013 3:25:36 PM	9/3/2013 6:27:28 PM	3.03
11	zzz		9/5/2013 3:05:08 PM	No Log Out	0
12	zzz		9/6/2013 3:39:57 PM	No Log Out	0
13	zzz		9/7/2013 3:59:26 PM	No Log Out	0
14	aaa		9/2/2013 2:53:34 PM	No Log Out	0
15	aaa		9/3/2013 3:26:27 PM	No Log Out	0
16	aaa		9/4/2013 3:00:42 PM	No Log Out	0
17	aaa		9/5/2013 3:03:58 PM	No Log Out	0
18	aaa		9/6/2013 2:41:42 PM	9/7/2013 12:02:56 AM	9.35
19	aaa		9/7/2013 3:35:38 PM	No Log Out	0

<tbody>
</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