Cleaning up data
 

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!)


---
Message posted from http://www.ExcelForum.com/

Cleaning up data
 

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


--
Regards,
Tom Ogilvy




"ali" wrote in message
...
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!)


---
Message posted from http://www.ExcelForum.com/

Cleaning up data
 

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


In article ,
ali wrote:

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?

Cleaning up data
 

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"ali" wrote in message ...
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!)


---
Message posted from http://www.ExcelForum.com/

Cleaning up data
 

Title:="ColorCells()", _

Recycling old code? <g

--
Regards,
Tom Ogilvy

"J.E. McGimpsey" wrote in message
...
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


In article ,
ali wrote:

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?

Cleaning up data
 

Guilty, guilty, guilty!

In article ,
"Tom Ogilvy" wrote:

Recycling old code? <g