I receive a worksheet daily from one of our outside companies. Because of
the way they have to get the information from their sources the data in one
of the columns is filled with alot of junk and I'm manually deleting the
information that's not needed. Each cells data is varied from one row to the
next. Is there a formula or feature in Excel that will remove all the
unwanted data? Sample data is included.

Cell C2 reads: 23477 1ea, 2489 1ea, 209667 1ea, 2096711 2ea, 79647 1ea.
Cell C3 reads: Item 694 ( Qty 2)<br 80022 (7)<br 209667 (2)<br <br

I don't need any of the letters nor parenthesis, brackets, commas,
etc...just the numbers...see example below

Cell C2 should read as: 23477 1 2489 1 209667 1 2096711 2 79647 1
Cell C3 should read as: 694 2 80022 7 209667 2

Any advice?

Rick

Try this:

Name the range you want to have cleaned "rng_to_Clean".

Then copy and paste the following macro into a new Module.

Finally, run the macro.

I think it'll do the trick.

Good Luck.


Sub Clean_Range()
Dim c As Range
Dim i As Integer
Dim strNew As String
strNew = "'"
For Each c In Range("rng_to_Clean")
For i = 1 To Len(c.Value)
If IsNumeric(Mid(c, i, 1)) Or Mid(c, i, 1) = " " Then
If strNew = "'" And Mid(c, i, 1) = " " Then
Else
strNew = strNew & Mid(c, i, 1)
End If
End If
Next i
c.Value = Trim(strNew)
strNew = "'"
Next c
End Sub

Thanks for the response...unfortunately it didn't work...named the
range...created the module, copied/pasted the macro into the module and went
to run and nothing...

"ND Pard" wrote:

Try this:

Name the range you want to have cleaned "rng_to_Clean".

Then copy and paste the following macro into a new Module.

Finally, run the macro.

I think it'll do the trick.

Good Luck.


Sub Clean_Range()
Dim c As Range
Dim i As Integer
Dim strNew As String
strNew = "'"
For Each c In Range("rng_to_Clean")
For i = 1 To Len(c.Value)
If IsNumeric(Mid(c, i, 1)) Or Mid(c, i, 1) = " " Then
If strNew = "'" And Mid(c, i, 1) = " " Then
Else
strNew = strNew & Mid(c, i, 1)
End If
End If
Next i
c.Value = Trim(strNew)
strNew = "'"
Next c
End Sub

Sorry ... I entered the data in cells c2 & c3 as per your original post,
named the range and ran the macro. It worked GREAT. I'm using Excel 2007 on
Windows XP.

"DipyDawg" wrote:

Thanks for the response...unfortunately it didn't work...named the
range...created the module, copied/pasted the macro into the module and went
to run and nothing...

"ND Pard" wrote:

Try this:

Name the range you want to have cleaned "rng_to_Clean".

Then copy and paste the following macro into a new Module.

Finally, run the macro.

I think it'll do the trick.

Good Luck.


Sub Clean_Range()
Dim c As Range
Dim i As Integer
Dim strNew As String
strNew = "'"
For Each c In Range("rng_to_Clean")
For i = 1 To Len(c.Value)
If IsNumeric(Mid(c, i, 1)) Or Mid(c, i, 1) = " " Then
If strNew = "'" And Mid(c, i, 1) = " " Then
Else
strNew = strNew & Mid(c, i, 1)
End If
End If
Next i
c.Value = Trim(strNew)
strNew = "'"
Next c
End Sub

Not sure what my problem is...only thing different is I'm operating off of
Excel 2003 on XP...tried it again and same thing...just sits there...not even
a flicker...

"ND Pard" wrote:

Sorry ... I entered the data in cells c2 & c3 as per your original post,
named the range and ran the macro. It worked GREAT. I'm using Excel 2007 on
Windows XP.

"DipyDawg" wrote:

Thanks for the response...unfortunately it didn't work...named the
range...created the module, copied/pasted the macro into the module and went
to run and nothing...

"ND Pard" wrote:

Try this:

Name the range you want to have cleaned "rng_to_Clean".

Then copy and paste the following macro into a new Module.

Finally, run the macro.

I think it'll do the trick.

Good Luck.


Sub Clean_Range()
Dim c As Range
Dim i As Integer
Dim strNew As String
strNew = "'"
For Each c In Range("rng_to_Clean")
For i = 1 To Len(c.Value)
If IsNumeric(Mid(c, i, 1)) Or Mid(c, i, 1) = " " Then
If strNew = "'" And Mid(c, i, 1) = " " Then
Else
strNew = strNew & Mid(c, i, 1)
End If
End If
Next i
c.Value = Trim(strNew)
strNew = "'"
Next c
End Sub