I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How can
I remove these? They are making my formulas not work because excel doesn't
recognize the numbers.

Thanks.

=--RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))))


try this for the numbers.

"Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How can
I remove these? They are making my formulas not work because excel doesn't
recognize the numbers.

Thanks.

First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How can
I remove these? They are making my formulas not work because excel doesn't
recognize the numbers.

Thanks.

The symbols are visible in the cells. I tried the macro and it stripped out
too much. For example, some of the cells have strings of text and it took
out all the spaces between the words. Also, it did something to all the
numbers....where there was $10.00, it changed it to 1000.00


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How
can
I remove these? They are making my formulas not work because excel
doesn't
recognize the numbers.

Thanks.

Dan

Hopefully you closed the workbook without saving<g

I will go over the first macro to see what's changing the $10.00 to 1000.00 and
make changes. Glad you pointed that out.

Try this macro on a copy of the worksheet. You will have to run it twice. Once
for " and once for ^

Public Sub Strip_Pick()
''strips out whichever character you input
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & _
"," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
pick = InputBox("Enter the character to strip out")
gett = Asc(UCase(pick))
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) = gett) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
Selection.Replace What:=" ", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord

On Thu, 8 Feb 2007 14:08:35 -0700, "Dan B" wrote:

The symbols are visible in the cells. I tried the macro and it stripped out
too much. For example, some of the cells have strings of text and it took
out all the spaces between the words. Also, it did something to all the
numbers....where there was $10.00, it changed it to 1000.00


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How
can
I remove these? They are making my formulas not work because excel
doesn't
recognize the numbers.

Thanks.

I didn't save the changes...no worries there.

I tried the new macro for removing ^ and ", but it didn't remove them.
I may have steered you in the wrong direction....these symbols are visible
in the cell IF you are editing the cell. If not you can't see them. I had
hit F2 on a selected cell, and I could see it. Sorry about that.

So, I tried the Tools, Options, Transitions thing and it removed that
symbols. How would I trim the extra space in front of each number?

Thanks for your help.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Dan

Hopefully you closed the workbook without saving<g

I will go over the first macro to see what's changing the $10.00 to
1000.00 and
make changes. Glad you pointed that out.

Try this macro on a copy of the worksheet. You will have to run it twice.
Once
for " and once for ^

Public Sub Strip_Pick()
''strips out whichever character you input
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & _
"," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
pick = InputBox("Enter the character to strip out")
gett = Asc(UCase(pick))
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) = gett) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
Selection.Replace What:=" ", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord

On Thu, 8 Feb 2007 14:08:35 -0700, "Dan B" wrote:

The symbols are visible in the cells. I tried the macro and it stripped
out
too much. For example, some of the cells have strings of text and it took
out all the spaces between the words. Also, it did something to all the
numbers....where there was $10.00, it changed it to 1000.00


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How
can
I remove these? They are making my formulas not work because excel
doesn't
recognize the numbers.

Thanks.

Have you tried using the numbers as is? Do they still act as if they were text?

If so, you should see......in the formula bar..........an apostrophe preceding
the number which idicates it is text.

i.e. ' 1234

If so............................

Assuming data is in column A

In B1 enter =TRIM(A1) and copy down.

When done, select column B and CopyPaste Special(in place)ValuesOKEsc.

Delete column A


Gord


On Thu, 8 Feb 2007 15:27:24 -0700, "Dan B" wrote:

I didn't save the changes...no worries there.

I tried the new macro for removing ^ and ", but it didn't remove them.
I may have steered you in the wrong direction....these symbols are visible
in the cell IF you are editing the cell. If not you can't see them. I had
hit F2 on a selected cell, and I could see it. Sorry about that.

So, I tried the Tools, Options, Transitions thing and it removed that
symbols. How would I trim the extra space in front of each number?

Thanks for your help.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Dan

Hopefully you closed the workbook without saving<g

I will go over the first macro to see what's changing the $10.00 to
1000.00 and
make changes. Glad you pointed that out.

Try this macro on a copy of the worksheet. You will have to run it twice.
Once
for " and once for ^

Public Sub Strip_Pick()
''strips out whichever character you input
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & _
"," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
pick = InputBox("Enter the character to strip out")
gett = Asc(UCase(pick))
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) = gett) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
Selection.Replace What:=" ", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord

On Thu, 8 Feb 2007 14:08:35 -0700, "Dan B" wrote:

The symbols are visible in the cells. I tried the macro and it stripped
out
too much. For example, some of the cells have strings of text and it took
out all the spaces between the words. Also, it did something to all the
numbers....where there was $10.00, it changed it to 1000.00


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^ in
front. I tried find and replace and it didn't find these symbols. How
can
I remove these? They are making my formulas not work because excel
doesn't
recognize the numbers.

Thanks.

That works. Thank you so much!!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Have you tried using the numbers as is? Do they still act as if they were
text?

If so, you should see......in the formula bar..........an apostrophe
preceding
the number which idicates it is text.

i.e. ' 1234

If so............................

Assuming data is in column A

In B1 enter =TRIM(A1) and copy down.

When done, select column B and CopyPaste Special(in place)ValuesOKEsc.

Delete column A


Gord


On Thu, 8 Feb 2007 15:27:24 -0700, "Dan B" wrote:

I didn't save the changes...no worries there.

I tried the new macro for removing ^ and ", but it didn't remove them.
I may have steered you in the wrong direction....these symbols are visible
in the cell IF you are editing the cell. If not you can't see them. I
had
hit F2 on a selected cell, and I could see it. Sorry about that.

So, I tried the Tools, Options, Transitions thing and it removed that
symbols. How would I trim the extra space in front of each number?

Thanks for your help.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Dan

Hopefully you closed the workbook without saving<g

I will go over the first macro to see what's changing the $10.00 to
1000.00 and
make changes. Glad you pointed that out.

Try this macro on a copy of the worksheet. You will have to run it
twice.
Once
for " and once for ^

Public Sub Strip_Pick()
''strips out whichever character you input
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & _
"," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
pick = InputBox("Enter the character to strip out")
gett = Asc(UCase(pick))
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) = gett) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
Selection.Replace What:=" ", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord

On Thu, 8 Feb 2007 14:08:35 -0700, "Dan B" wrote:

The symbols are visible in the cells. I tried the macro and it stripped
out
too much. For example, some of the cells have strings of text and it
took
out all the spaces between the words. Also, it did something to all the
numbers....where there was $10.00, it changed it to 1000.00


"Gord Dibben" <gorddibbATshawDOTca wrote in message
m...
First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^
in
front. I tried find and replace and it didn't find these symbols.
How
can
I remove these? They are making my formulas not work because excel
doesn't
recognize the numbers.

Thanks.

Happy to hear you're sorted.

Gord

On Thu, 8 Feb 2007 17:21:59 -0700, "Dan B" wrote:

That works. Thank you so much!!


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Have you tried using the numbers as is? Do they still act as if they were
text?

If so, you should see......in the formula bar..........an apostrophe
preceding
the number which idicates it is text.

i.e. ' 1234

If so............................

Assuming data is in column A

In B1 enter =TRIM(A1) and copy down.

When done, select column B and CopyPaste Special(in place)ValuesOKEsc.

Delete column A


Gord


On Thu, 8 Feb 2007 15:27:24 -0700, "Dan B" wrote:

I didn't save the changes...no worries there.

I tried the new macro for removing ^ and ", but it didn't remove them.
I may have steered you in the wrong direction....these symbols are visible
in the cell IF you are editing the cell. If not you can't see them. I
had
hit F2 on a selected cell, and I could see it. Sorry about that.

So, I tried the Tools, Options, Transitions thing and it removed that
symbols. How would I trim the extra space in front of each number?

Thanks for your help.


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Dan

Hopefully you closed the workbook without saving<g

I will go over the first macro to see what's changing the $10.00 to
1000.00 and
make changes. Glad you pointed that out.

Try this macro on a copy of the worksheet. You will have to run it
twice.
Once
for " and once for ^

Public Sub Strip_Pick()
''strips out whichever character you input
Dim myRange As Range
Dim cell As Range
Dim myStr As String
Dim i As Integer
With Application
.ScreenUpdating = False
.Calculation = xlManual
End With
On Error Resume Next
Set myRange = Range(ActiveCell.Address & _
"," & Selection.Address) _
.SpecialCells(xlCellTypeConstants)
If myRange Is Nothing Then Exit Sub
If Not myRange Is Nothing Then
pick = InputBox("Enter the character to strip out")
gett = Asc(UCase(pick))
For Each cell In myRange
myStr = cell.text
For i = 1 To Len(myStr)
If (Asc(UCase(Mid(myStr, i, 1))) = gett) Then
myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1)
End If
Next i
cell.Value = Application.Trim(myStr)
Next cell
End If
Selection.Replace What:=" ", _
Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
With Application
.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub


Gord

On Thu, 8 Feb 2007 14:08:35 -0700, "Dan B" wrote:

The symbols are visible in the cells. I tried the macro and it stripped
out
too much. For example, some of the cells have strings of text and it
took
out all the spaces between the words. Also, it did something to all the
numbers....where there was $10.00, it changed it to 1000.00


"Gord Dibben" <gorddibbATshawDOTca wrote in message
om...
First question...............

Do these marks show in the cell or just in the Formula Bar?

If the latter, they are Lotus formatting marks.

ToolsOptionsTransition, Uncheck all the Transition settings.

Then use the TRIM function to remove extra spaces.

If marks are actually visible in the cells, run this macro.

Public Sub StripAll_But_NumText()
Dim rConsts As Range
Dim rCell As Range
Dim i As Long
Dim sChar As String
Dim sTemp As String

On Error Resume Next
Set rConsts = Selection.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rConsts Is Nothing Then
For Each rCell In rConsts
With rCell
For i = 1 To Len(.text)
sChar = Mid(.text, i, 1)
If sChar Like "[0-9a-zA-Z]" Then _
sTemp = sTemp & sChar
Next i
.Value = sTemp
End With
sTemp = ""
Next rCell
End If
End Sub


Gord Dibben MS Excel MVP

On Thu, 8 Feb 2007 10:06:17 -0700, "Dan B" wrote:

I copied some data from a report on a web page into excel.

The numbers have a " and a space in front of them, the dates have a ^
in
front. I tried find and replace and it didn't find these symbols.
How
can
I remove these? They are making my formulas not work because excel
doesn't
recognize the numbers.

Thanks.