Changing a text field to a number

  • Thread starter Thread starter Michel Khennafi
  • Start date Start date
M

Michel Khennafi

Hello there!

I imported a MS Project file in excel and I have values in cell like 3.5d (3
and a half days)...I would like to browse all cells and remove the day to
get only the numbers...
The 'd' is always the last character in the cell... I tried using a macro
but failed...

Has anyone any idea about how to browse a column (top to bottom), remove all
the "d" and go to the next column on top

Thank you so muchfor your precious help

Michel
 
Hello there!

I imported a MS Project file in excel and I have values in cell like 3.5d (3
and a half days)...I would like to browse all cells and remove the day to
get only the numbers...
The 'd' is always the last character in the cell... I tried using a macro
but failed...

Has anyone any idea about how to browse a column (top to bottom), remove all
the "d" and go to the next column on top

Thank you so muchfor your precious help

Michel
Click to expand...

Try this macro. First select the area on your worksheet that contains the data
in the format you wish to change. The macro tests that each cell ends in a 'd'
and that the remainder of the cell is a number. If so, it strips of the d and
converts the remainder to a number.

=============
Sub RemoveD()
Dim c As Range

For Each c In Selection
If Right(c.Text, 1) = "d" Or Right(c.Text, 1) = "D" Then
If IsNumeric(Left(c.Text, Len(c.Text) - 1)) Then
c.Value = CDbl(Left(c.Text, Len(c.Text) - 1))
End If
End If
Next c
End Sub
============

--ron
 
Back
Top