Removing text

  • Thread starter Thread starter Kurt Radecki
  • Start date Start date
K

Kurt Radecki

Hello-

Is there a simple macro already written that removes text from cells? My
situation has me importing data into a spreadsheet from another
application. This application stores time information, a duration of
time, with the corresponding unit, hours, days, etc. For example, 2 hrs,
1 hr, 5 days, 1 day, etc. Unfortunately this is also how it imports into
Excel. And, I'm actually doing a copy-paste, not an import.

It's useful for me to add the durations. In order to do this, I can
think of only one way, to remove the text that represents the unit.
Having not worked with the macro language in Excel, I'm curious if there
is code already written to do this.

Thanks.
-Kurt
 
Kurt,

This will take imported data from column 3 and put the value in column 1 and
the unit in column 2.
(Assumes the data starts on Row 1. If not, have the For/Next loop start
with the Row number of the first row of data).

Sub Tester()

LastRow = Cells(65536, 3).End(xlUp).Row
For i = 1 To LastRow
sString = Cells(i, 3)
Cells(i, 1) = Val(sString)
sUnit = Trim(Mid(sString, 2))

'If unit is not plural, make it plural
If Right(sUnit, 1) <> "s" Then _
sUnit = sUnit & "s"

Cells(i, 2) = sUnit
Next i

End Sub

HTH,
Shockley
 
I like:


Sub changeTexttoValue()
'Replaces the "unit" with a value for the unit
'I.E. day = 24 hrs
Dim cell As Range
Dim MyRange As Range
Dim ValueinCell As Variant
Application.Sheets("Sheet1").Select
Range("A1").SpecialCells(xlLastCell).Select
Set MyRange = Range(Cells(1, 1), _
Cells(ActiveCell.Row, ActiveCell.Column))
For Each cell In MyRange
ValueinCell = Val(cell.Value)
If InStr(cell.Value, "day") Then
ValueinCell = ValueinCell * 24
End If
If InStr(cell.Value, "hr") Then
ValueinCell = ValueinCell
End If

'For testing
If cell.Value <> "" Then
Debug.Print cell.Address, ValueinCell
End If

'Replaces the value in each cell
If cell.Value <> "" Then
cell.Value = ValueinCell
End If

Next cell
Range("A1").Select
End Sub

Ken
 
Perfect! Thanks so much. -Kurt

Kurt,

This will take imported data from column 3 and put the value in column 1 and
the unit in column 2.
(Assumes the data starts on Row 1. If not, have the For/Next loop start
with the Row number of the first row of data).

Sub Tester()

LastRow = Cells(65536, 3).End(xlUp).Row
For i = 1 To LastRow
sString = Cells(i, 3)
Cells(i, 1) = Val(sString)
sUnit = Trim(Mid(sString, 2))

'If unit is not plural, make it plural
If Right(sUnit, 1) <> "s" Then _
sUnit = sUnit & "s"

Cells(i, 2) = sUnit
Next i

End Sub

HTH,
Shockley
Click to expand...
 
Back
Top