Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Square before beginning of a new line
I have an access database which generates an excel document. The excel sheet
is populated from text boxes on an access form. These access textboxes can accommodate multiple lines. When the content of a given multiline access textbox is placed into an excel sheet, a square can be observed just before the beginning of a new line. I would to keep the multiline structure in the cell without the square. I have noticed that each line is created with chr(13) & chr(10). The square only disappears if I completely remove chr(13) & chr(10) with the replace function. However this gets rid of the new line too. How can I get rid of the square while keeping the multiline structure in the excel cell? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Square before beginning of a new line
=Char(10) (in excel) or chr(10) or vblf in VBA is the newline character (but
only if the cell has wraptext enabled). =char(13), chr(13), vbcr is a character that'll appear as that square. 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.aspx Depending on what that character is, you may be able to use alt-#### (from the number keypad) to enter the character into the Other box in the text to columns wizard dialog. In fact, you may be able to select the character (in the formula bar), and copy it. Then use ctrl-v to paste into that text to columns Other box. You may be able to use Edit|Replace to change the character--Some characters can be entered by holding the alt-key and typing the hex number on the numeric keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've never been able to get alt-0013 to work for carriage returns. Another alternative is to fix it via a formula: =substitute(a1,char(##),"") Replace ## 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(##)) '<--What showed up in CellView? myGoodChars = Array("") 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: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Jess wrote: I have an access database which generates an excel document. The excel sheet is populated from text boxes on an access form. These access textboxes can accommodate multiple lines. When the content of a given multiline access textbox is placed into an excel sheet, a square can be observed just before the beginning of a new line. I would to keep the multiline structure in the cell without the square. I have noticed that each line is created with chr(13) & chr(10). The square only disappears if I completely remove chr(13) & chr(10) with the replace function. However this gets rid of the new line too. How can I get rid of the square while keeping the multiline structure in the excel cell? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Line Chart with different beginning values | Charts and Charting in Excel | |||
eliminate space at beginning of line | Excel Worksheet Functions | |||
Sort beginning one-line BELOW what should be | Excel Programming | |||
How do I calculate the Root Mean Square (RMS) of line of equality | Excel Discussion (Misc queries) | |||
Line break in cell, char(10) square settings? | Excel Programming |