Find enter stroke and add text

[Excel_Blonde]

Hi There,

I'm looking for a VBA script (or formula) to find an enter stroke and add text.

E.g

Sample Text
123
abc

Def
GHI

To appear:

Sample Text\par
123\par
abc\par
\par
Def\par
GHI\par

Apologies if this question has previously been asked and answered, I cant find anything on the forums but may be using the wrong words in my searches.

 

[Fluff]

Try Ctrl H
Find what > Ctrl J
replace > \par
Make sure that match cell contents is unchecked
Replace all

 

[DanteAmor]

Assuming that your data starts in A1 put the following formula in B1

=A1&"\par"

Copy the formula down

 

[jtakw]

Hi,

Assuming you mean the sample data shown is all in a Single cell, separated by linefeed, formula option:

Book1
	A	B
1	Sample Text 123 abc Def GHI	Sample Text\par 123\par abc\par \par Def\par GHI\par
Sheet637

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,CHAR(10),"\par"&CHAR(10))&"\par"

 

[Excel_Blonde]

Hi,

Assuming you mean the sample data shown is all in a Single cell, separated by linefeed, formula option:

	A	B
Sample Text 123 abc Def GHI	Sample Text\par 123\par abc\par \par Def\par GHI\par

<tbody>
[TD="align: center"]1[/TD]

</tbody>

Sheet637​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=SUBSTITUTE(A1,CHAR(10),"\par"&CHAR(10))&"\par"[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This works perfectly. Thank you all for your suggestions, this has saved me hours if not days of manual input.

 

[jtakw]

You're welcome, thanks for the feedback.