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)

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

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?

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

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