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
1	Sample Text 123 abc Def GHI	Sample Text\par 123\par abc\par \par Def\par GHI\par

<tbody>
</tbody>

Sheet637​

Worksheet Formulas Cell Formula B1 =SUBSTITUTE(A1,CHAR(10),"\par"&CHAR(10))&"\par" <tbody> </tbody>

<tbody>
</tbody>

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.