Hi all,

I have this text in a number of cells typed in as e.g. ".09 1/4" which in
numeric terms is 0.0925.

My questions is, can someone please help me think of a simple macro to
automatically covert the cell to a numeric.

Many thanks in advance.
Ray

One way:

Dim rCell As Range
Dim n As Long
For Each rCell In Selection.Cells
With rCell
n = InStr(.Text, " ")
If n 0 Then
On Error Resume Next
.Value = CDbl(Left(.Text, n - 1) & _
Mid(Evaluate(Mid(.Text, n + 1)), 3))
On Error GoTo 0
End If
End With
Next rCell


In article ,
swiftcode wrote:

Hi all,

I have this text in a number of cells typed in as e.g. ".09 1/4" which in
numeric terms is 0.0925.

My questions is, can someone please help me think of a simple macro to
automatically covert the cell to a numeric.

Many thanks in advance.
Ray

Thanks JE, it works great.

Have a good day ahead.

Rgds
Ray

"JE McGimpsey" wrote:

One way:

Dim rCell As Range
Dim n As Long
For Each rCell In Selection.Cells
With rCell
n = InStr(.Text, " ")
If n 0 Then
On Error Resume Next
.Value = CDbl(Left(.Text, n - 1) & _
Mid(Evaluate(Mid(.Text, n + 1)), 3))
On Error GoTo 0
End If
End With
Next rCell


In article ,
swiftcode wrote:

Hi all,

I have this text in a number of cells typed in as e.g. ".09 1/4" which in
numeric terms is 0.0925.

My questions is, can someone please help me think of a simple macro to
automatically covert the cell to a numeric.

Many thanks in advance.
Ray

Dim posSpace As Long
Dim posSlash As Long
Dim numChars As Long
Dim tmp As Variant

With ActiveCell

numChars = Len(.Text)
posSpace = InStr(.Text, " ")
posSlash = InStr(.Text, "/")
tmp = Mid(.Text, posSlash - 1, posSlash - posSpace - 1) / Mid(.Text,
numChars, posSlash - 1)
tmp = Left$(.Text, posSpace - 1) & tmp * 100
.Value = Val(tmp)
End With


--
__________________________________
HTH

Bob

"swiftcode" wrote in message
...
Hi all,

I have this text in a number of cells typed in as e.g. ".09 1/4" which in
numeric terms is 0.0925.

My questions is, can someone please help me think of a simple macro to
automatically covert the cell to a numeric.

Many thanks in advance.
Ray

provided yr data always follows that same pattern you might also try a
formula:

=LEFT(A1,FIND(" ",A1)-1)+(LEFT(RIGHT(A1,FIND(" ",A1)-1),1)/RIGHT(RIGHT
(A1,FIND(" ",A1)-1),1))/100



On 14 Sty, 08:33, swiftcode
wrote:
Hi all,

I have this text in a number of cells typed in as e.g. ".09 1/4" which in
numeric terms is 0.0925.

My questions is, can someone please help me think of a simple macro to
automatically covert the cell to a numeric.

Many thanks in advance.
Ray