Cleaning up data

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

Hey everyone,

Do any of you know a macro that will allow me to delete the first 'n'
characters of a cell? Basically i get sent data that often has varying
amounts of useless data in front of the data i need and i need to find
a way to clean it up!

I will need to be able to enter the number of leading characters to
remove from the cell but don't have the first idea how to create
anything like this?

Thanks guys (and girls!)
 
Sub AATest1()
Dim num As Long
Dim res As Variant
Dim sStr As String
res = InputBox("enter number of characters")
If res = "" Then Exit Sub
If Not IsNumeric(res) Then Exit Sub
num = res
For Each cell In Selection
sStr = cell.Text
sStr = Right(sStr, Len(sStr) - num)
cell.Value = sStr
Next

End Sub
 
One way:

Public Sub DeleteFirstNChars()
Dim result As Variant
Dim rCell As Range
Do
result = Application.InputBox( _
Prompt:="Enter number of chars", _
Title:="ColorCells()", _
Default:=3, _
Type:=1)
If result = False Then Exit Sub 'user clicked Cancel
Loop Until result > 0
On Error Resume Next
For Each rCell In Cells.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With rCell
.Value = Mid(.Text, result + 1)
End With
Next rCell
On Error Resume Next
End Sub
 
Try this for the activecell

Sub test()
Dim mynum As Integer
mynum = Application.InputBox("Enter a number", Type:=1)
ActiveCell.Value = Right(ActiveCell.Value, Len(ActiveCell.Value) - mynum)
End Sub
 
Back
Top