Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alt+Enter character
A cell contains:
PROD.FTPLIB(CRTUPL01)PROD.FTPLIB(CRTUPL02)PROD.FTP LIB(CRTUPL03) What do I type in the REPLACE WITH: box so each right parenthesis is replaced with a right parenthesis followed by a carriage-return character so the result is: PROD.FTPLIB(CRTUPL01) PROD.FTPLIB(CRTUPL02) PROD.FTPLIB(CRTUPL03) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alt+Enter character
Hi,
Am Mon, 8 Dec 2014 10:38:06 -0800 (PST) schrieb GWC: What do I type in the REPLACE WITH: box so each right parenthesis is replaced with a right parenthesis followed by a carriage-return character so the result is: PROD.FTPLIB(CRTUPL01) PROD.FTPLIB(CRTUPL02) PROD.FTPLIB(CRTUPL03) if you replace ")" with ")" Alt0010 you have a new line behind the string. Try it with a macro: Sub Test() Dim len1 As Long, len2 As Long With Range("A1") len1 = InStr(.Value, ")") len2 = InStr(len1 + 1, .Value, ")") .Value = Left(.Value, len1) & Chr(10) & _ Mid(.Value, len1 + 1, len2 - len1) & Chr(10) & _ Mid(.Value, len2 + 1) .WrapText = True .EntireRow.AutoFit .EntireColumn.AutoFit End With End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alt+Enter character
Hi Claus B.,
Replace ")" with ")" Alt0010 worked great but now: How can I retain the carriage-returns if I type =LEFTA(A1,60) in column B1 and =mid(a1,61,100) in column C1? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alt+Enter character
Hi,
Am Mon, 8 Dec 2014 16:48:08 -0800 (PST) schrieb GWC: How can I retain the carriage-returns if I type =LEFTA(A1,60) in column B1 and =mid(a1,61,100) in column C1? replace ")" with ")"Alt0010 and write in B1: =LEFT(A1,LEN(A1)-1) to delete the last new line Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alt+Enter character
Hi again,
Am Mon, 8 Dec 2014 16:48:08 -0800 (PST) schrieb GWC: How can I retain the carriage-returns if I type =LEFTA(A1,60) in column B1 and =mid(a1,61,100) in column C1? you can also work with TextToColumns. Set ")" as separator and choose in step 3 B1 as target cell. Now you have your substrings in B1:D1. Then write in A1: =B1&CHAR(10)&C1&CHAR(10)&D1 Copy A1 and paste special as values that you can delete B1:D1 Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alt Enter Creates Tab Character | Excel Discussion (Misc queries) | |||
How to remove Alt+Enter character in a worksheet? | Excel Discussion (Misc queries) | |||
How do I enter a slash character in excel? | Excel Discussion (Misc queries) | |||
Alt + Enter Character Number | Excel Programming | |||
DDE an Alt-Enter (Newline) character | Excel Programming |