Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to remove characters
I have an Excel Workbook where the sheets are all linked to one main sheet.
The data on the main sheet ends up with character10 (the little square box) in each cell. I have a macro that I run and it deletes all the characters for me, its great I love it, but it also deletes all the formulas on that sheet which I need. How can I delete that character without deleting my formulas. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to remove characters
It would help to see the code for the macro you are currently using that is
also destroying your formulas. We could probably fix it easily. I suspect your macro is simply removing the right-most character from what ever is in the cell. You need to test the value in a cell before stripping that character to see that it is CHR(10). "YanYan" wrote in message ... I have an Excel Workbook where the sheets are all linked to one main sheet. The data on the main sheet ends up with character10 (the little square box) in each cell. I have a macro that I run and it deletes all the characters for me, its great I love it, but it also deletes all the formulas on that sheet which I need. How can I delete that character without deleting my formulas. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to remove characters
Yes it is CHR10, the character is appearing at the beginning and other times
at the end of my data. This is the macro that I am using to remove it: Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Value, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub This macro removes my formulas. "JLatham" wrote: It would help to see the code for the macro you are currently using that is also destroying your formulas. We could probably fix it easily. I suspect your macro is simply removing the right-most character from what ever is in the cell. You need to test the value in a cell before stripping that character to see that it is CHR(10). "YanYan" wrote in message ... I have an Excel Workbook where the sheets are all linked to one main sheet. The data on the main sheet ends up with character10 (the little square box) in each cell. I have a macro that I run and it deletes all the characters for me, its great I love it, but it also deletes all the formulas on that sheet which I need. How can I delete that character without deleting my formulas. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to remove characters
Revised.................change Cell.Value to Cell.Formula
Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Formula, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub Gord Dibben MS Excel MVP On Fri, 8 Jun 2007 16:59:02 -0700, YanYan wrote: Yes it is CHR10, the character is appearing at the beginning and other times at the end of my data. This is the macro that I am using to remove it: Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Value, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub This macro removes my formulas. "JLatham" wrote: It would help to see the code for the macro you are currently using that is also destroying your formulas. We could probably fix it easily. I suspect your macro is simply removing the right-most character from what ever is in the cell. You need to test the value in a cell before stripping that character to see that it is CHR(10). "YanYan" wrote in message ... I have an Excel Workbook where the sheets are all linked to one main sheet. The data on the main sheet ends up with character10 (the little square box) in each cell. I have a macro that I run and it deletes all the characters for me, its great I love it, but it also deletes all the formulas on that sheet which I need. How can I delete that character without deleting my formulas. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to remove characters
The formula didnt do anything even though all we did was replace value to
formula. Maybe I am leaving out critical information that I am not aware of, would someone be willing to look at my Excel Grid that I am trying to fix. "Gord Dibben" wrote: Revised.................change Cell.Value to Cell.Formula Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Formula, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub Gord Dibben MS Excel MVP On Fri, 8 Jun 2007 16:59:02 -0700, YanYan wrote: Yes it is CHR10, the character is appearing at the beginning and other times at the end of my data. This is the macro that I am using to remove it: Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Value, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub This macro removes my formulas. "JLatham" wrote: It would help to see the code for the macro you are currently using that is also destroying your formulas. We could probably fix it easily. I suspect your macro is simply removing the right-most character from what ever is in the cell. You need to test the value in a cell before stripping that character to see that it is CHR(10). "YanYan" wrote in message ... I have an Excel Workbook where the sheets are all linked to one main sheet. The data on the main sheet ends up with character10 (the little square box) in each cell. I have a macro that I run and it deletes all the characters for me, its great I love it, but it also deletes all the formulas on that sheet which I need. How can I delete that character without deleting my formulas. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to remove characters
You are trying to get replace line feeds...........chr(10)........in selected
cells with a space. Right? Are the line feeds generated by formula like =A1 & CHAR()10) & B1 ? Or generated some other way like manually entered Alt + Enters or from imported data? If the latter, the macro will work. If you want to send me the workbook, feel free to do so. Change the AT and DOT to appropriate punctuation to get my address. Gord On Tue, 12 Jun 2007 15:44:00 -0700, YanYan wrote: The formula didnt do anything even though all we did was replace value to formula. Maybe I am leaving out critical information that I am not aware of, would someone be willing to look at my Excel Grid that I am trying to fix. "Gord Dibben" wrote: Revised.................change Cell.Value to Cell.Formula Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Formula, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub Gord Dibben MS Excel MVP On Fri, 8 Jun 2007 16:59:02 -0700, YanYan wrote: Yes it is CHR10, the character is appearing at the beginning and other times at the end of my data. This is the macro that I am using to remove it: Sub ReplaceCR() Dim rCell As Range Dim K As Long Dim NewString As String For Each rCell In Selection.Cells For K = 1 To Len(rCell.Value) If Mid(rCell.Value, K, 1) < Chr(10) Then NewString = NewString & Mid(rCell.Value, K, 1) Else NewString = NewString & " " 'replacement space End If Next K rCell.Value = NewString NewString = "" Next rCell End Sub This macro removes my formulas. "JLatham" wrote: It would help to see the code for the macro you are currently using that is also destroying your formulas. We could probably fix it easily. I suspect your macro is simply removing the right-most character from what ever is in the cell. You need to test the value in a cell before stripping that character to see that it is CHR(10). "YanYan" wrote in message ... I have an Excel Workbook where the sheets are all linked to one main sheet. The data on the main sheet ends up with character10 (the little square box) in each cell. I have a macro that I run and it deletes all the characters for me, its great I love it, but it also deletes all the formulas on that sheet which I need. How can I delete that character without deleting my formulas. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to remove last 4 characters from the contents of a cell. | New Users to Excel | |||
Remove Characters from a cell | Excel Worksheet Functions | |||
Remove first few characters | Excel Worksheet Functions | |||
Remove top bit characters | Excel Discussion (Misc queries) | |||
I need to remove characters ... | Excel Discussion (Misc queries) |