Alt+Enter character

  • Thread starter Thread starter GWC
  • Start date Start date
G

GWC

A cell contains:

PROD.FTPLIB(CRTUPL01)PROD.FTPLIB(CRTUPL02)PROD.FTPLIB(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)
 
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)
Click to expand...

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.
 
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?
 
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?
Click to expand...

replace ")" with ")"Alt0010 and write in B1:
=LEFT(A1,LEN(A1)-1)
to delete the last new line


Regards
Claus B.
 
Back
Top