how to remove symbols ahead of text and numbers copied from web

D

Dan B

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.
 
G

Guest

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


try this for the numbers.
 
G

Gord Dibben

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

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

If the latter, they are Lotus formatting marks.

Tools>Options>Transition, 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
 
D

Dan B

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 said:
First question...............

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

If the latter, they are Lotus formatting marks.

Tools>Options>Transition, 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

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.
Click to expand...
Click to expand...
 
G

Gord Dibben

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

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 said:
First question...............

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

If the latter, they are Lotus formatting marks.

Tools>Options>Transition, 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

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dan B

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 said:
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

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 said:
First question...............

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

If the latter, they are Lotus formatting marks.

Tools>Options>Transition, 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


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.
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

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 Copy>Paste Special(in place)>Values>OK>Esc.

Delete column A


Gord


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 said:
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

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.

Tools>Options>Transition, 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


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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dan B

That works. Thank you so much!!


Gord Dibben said:
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 Copy>Paste Special(in place)>Values>OK>Esc.

Delete column A


Gord


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 said:
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


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.

Tools>Options>Transition, 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


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.
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

Happy to hear you're sorted.

Gord

That works. Thank you so much!!


Gord Dibben said:
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 Copy>Paste Special(in place)>Values>OK>Esc.

Delete column A


Gord


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


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.

Tools>Options>Transition, 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


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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copied from web page to excel sheet and cant remove from sheet 2
Trim and Substitute not working 4
symbols 8
Text to Number format 5
How to add a prefix to a column of numbers in an excel document? 4
copy numbers from numbers+text cell 3
Changing Text To Numbers ? 1
Can someone help me please? Prefix to add international diallingcodes to Uk mobile numbers 1

Top