Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
I have spent a good 8 hours on this over the last week and CANNOT figure this
out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Alt/F11 (to get to the VBE), then Ctrl/G (immediate window), then enter this
line: cells.Replace chr(10),"",xlpart --voila! "Luvsql" wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
EditReplace
What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Neither of these seem to work. I did do the edit replace and it said it
did find items to replace, yet, the data still appears as 21 Progress AvenueUnit8 (eg) and if I double click on the cell, the row height doubles 21 Progress Avenue Unit 8 "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Select the cells to fix (all of them???) and
format|cells|alignment tab|uncheck wrap text. Luvsql wrote: Neither of these seem to work. I did do the edit replace and it said it did find items to replace, yet, the data still appears as 21 Progress AvenueUnit8 (eg) and if I double click on the cell, the row height doubles 21 Progress Avenue Unit 8 "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Text wrap is not marked by default. I changed it to Chr(13) and it's removed
some of the formatting. Now, with cells that had more than 2 returns, it's a mess. Even if I backspace over data and re-type it is deleting letters and inserting spaces. This seems to be such a HUGE undertaking just to remove formatting. Why will edit Copy < paste special values not remove FORMATTING?? Even if I paste into notepad, copy, then paste back into a new cell it is still putting data with returns. "Dave Peterson" wrote: Select the cells to fix (all of them???) and format|cells|alignment tab|uncheck wrap text. Luvsql wrote: Neither of these seem to work. I did do the edit replace and it said it did find items to replace, yet, the data still appears as 21 Progress AvenueUnit8 (eg) and if I double click on the cell, the row height doubles 21 Progress Avenue Unit 8 "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Using the Alt then 0010 removes the issues with text that goes into 2 lines
123 Street Unit 5 becomes 123 StreetUnit 5 However, ones that have more than 2 lines, still have some sort of character within them that I can't remove. With Crystal reports, I had to remove both Chr10 and Chr 13. Is there an edit replace for both "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Why would pasting values change any format? If it did, then I think we'd have a
problem. I'm not sure what "I changed it to Chr(13)" means. What did you change and where did you change it? Saved from a previous post... Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(13),"") or =substitute(a1,char(13)," ") Replace 13 with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView? myGoodChars = Array(" ","") '<--what's the new character, "" for nothing? If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Luvsql wrote: Text wrap is not marked by default. I changed it to Chr(13) and it's removed some of the formatting. Now, with cells that had more than 2 returns, it's a mess. Even if I backspace over data and re-type it is deleting letters and inserting spaces. This seems to be such a HUGE undertaking just to remove formatting. Why will edit Copy < paste special values not remove FORMATTING?? Even if I paste into notepad, copy, then paste back into a new cell it is still putting data with returns. "Dave Peterson" wrote: Select the cells to fix (all of them???) and format|cells|alignment tab|uncheck wrap text. Luvsql wrote: Neither of these seem to work. I did do the edit replace and it said it did find items to replace, yet, the data still appears as 21 Progress AvenueUnit8 (eg) and if I double click on the cell, the row height doubles 21 Progress Avenue Unit 8 "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
I used this formula and it fixed it:
=CLEAN(SUBSTITUTE(A1,CHAR(10)," ")) "Dave Peterson" wrote: Why would pasting values change any format? If it did, then I think we'd have a problem. I'm not sure what "I changed it to Chr(13)" means. What did you change and where did you change it? Saved from a previous post... Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a1,char(13),"") or =substitute(a1,char(13)," ") Replace 13 with the ASCII value you see in Chip's addin. Or you could use a macro (after using Chip's CellView addin): Option Explicit Sub cleanEmUp() Dim myBadChars As Variant Dim myGoodChars As Variant Dim iCtr As Long myBadChars = Array(Chr(10), Chr(13)) '<--What showed up in CellView? myGoodChars = Array(" ","") '<--what's the new character, "" for nothing? If UBound(myGoodChars) < UBound(myBadChars) Then MsgBox "Design error!" Exit Sub End If For iCtr = LBound(myBadChars) To UBound(myBadChars) ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _ Replacement:=myGoodChars(iCtr), _ LookAt:=xlPart, SearchOrder:=xlByRows, _ MatchCase:=False Next iCtr End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Luvsql wrote: Text wrap is not marked by default. I changed it to Chr(13) and it's removed some of the formatting. Now, with cells that had more than 2 returns, it's a mess. Even if I backspace over data and re-type it is deleting letters and inserting spaces. This seems to be such a HUGE undertaking just to remove formatting. Why will edit Copy < paste special values not remove FORMATTING?? Even if I paste into notepad, copy, then paste back into a new cell it is still putting data with returns. "Dave Peterson" wrote: Select the cells to fix (all of them???) and format|cells|alignment tab|uncheck wrap text. Luvsql wrote: Neither of these seem to work. I did do the edit replace and it said it did find items to replace, yet, the data still appears as 21 Progress AvenueUnit8 (eg) and if I double click on the cell, the row height doubles 21 Progress Avenue Unit 8 "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing carriage returns from excel
Run this macro and see what happens.
Sub Remove_CR_LF() Selection.Replace What:=Chr(160), Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(13) & Chr(10), Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=Chr(10), Replacement:="", _ LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False End Sub BTW.........did you try Chip's CellView add-in as directed by Dave P? Gord On Tue, 20 Mar 2007 07:21:08 -0700, Luvsql wrote: Using the Alt then 0010 removes the issues with text that goes into 2 lines 123 Street Unit 5 becomes 123 StreetUnit 5 However, ones that have more than 2 lines, still have some sort of character within them that I can't remove. With Crystal reports, I had to remove both Chr10 and Chr 13. Is there an edit replace for both "Gord Dibben" wrote: EditReplace What: Alt + 0010(on the numpad) With: nothing Replace all. Gord Dibben MS Excel MVP On Mon, 19 Mar 2007 19:22:03 -0700, Luvsql wrote: I have spent a good 8 hours on this over the last week and CANNOT figure this out. I have exported all our contacts out of Outlook to excel and the addresses and some other fields are being exported with the carriage return, but you cannot see the symbol. If I export to tab delimited file, the ones with the carriage return are being put into 2 to 3 separate lines and makes importing impossible. For example, when I click on a field it actually appears as 123 Street Unit 4 So if it's exported to text, it messes up everything. I have tried every formula I can possible try and downloaded an add-on to view code and it says "cell has an apostrophe prefix" but cannot see that either. I can't even import into access and try to export to text as it still maintains this character somewhere. Please can someone please tell me how to formute the cell as 123 Street Unit 4? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting carriage returns in Excel cells | Excel Discussion (Misc queries) | |||
how do i format carriage returns for each cell in excel? | Excel Discussion (Misc queries) | |||
Carriage Returns Excel 2003 | Excel Discussion (Misc queries) | |||
Removing Carriage Returns in Excel 2000 | Excel Discussion (Misc queries) | |||
How do I automatically remove carriage returns in Excel? | Excel Discussion (Misc queries) |