Insert Line Break CHAR(10) Carriage return multiple times WITHIN a cell

[EpicRadB]

Hello All Excel Gurus out there,

I need help with designing VBA code to do the following:

Example	Desired Outcome
*First Line*Second Line*Third Line	*First Line *Second Line *Third Line
*First Line *Second Line*Third Line	*First Line *Second Line *Third Line

<colgroup><col><col></colgroup><tbody>
</tbody>

Basically, all I want to do is look inside the cell for all the * except the first one and replace that with a line break and a "*"

I have over 15,000 cells with a variable number of "*" and I do not want to place a line break if one already exist before the "*".

Thanks in advance for any help.

I am new to the forum. What is the best way to paste a table into the message?

 

[Fluff]

Hi & welcome to MrExcel.
I'm sure there must be a better way, but how about

Sub AddLF()
With Range("A1", Range("A" & Rows.Count).End(xlUp))
   .Replace "~*", Chr(10) & "*", xlPart
   .Replace Chr(10) & Chr(10), Chr(10), xlPart
   .Value = Evaluate(Replace("if(left(@,1)=char(10),right(@,len(@)-1),@)", "@", .Address))
End With
End Sub

 

[Rick Rothstein]

Here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub AddLFs()
  Dim Addr As String
  Addr = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Address
  Range(Addr) = Evaluate("MID(SUBSTITUTE(SUBSTITUTE(" & Addr & ",CHAR(10),""""),""*"",CHAR(10)&""*""),2,999)")
  Range(Addr).EntireRow.AutoFit
End Sub[/td]
[/tr]
[/table]

 

[Fluff]

Cheers Rick
I new there must have been an easier way to do it
Just couldn't figure it out

 

[EpicRadB]

Thank Fluff & Rick Both of those solutions worked for my purposes.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Bryman]

I am actually trying to do the exact same thing as EpicRadB with an extract from an archaic database, but the above code is not working.

When I execute the macro all the cell over my range convert to a #VALUE! like there original reference has been deleted or something.

One other thing would the code above be able to put just one Char(10) after if there were multiple ***** consecutively ?

 

[Fluff]

Can you post some sample data?
There are some tools to help with that, if you follow the link in my signature

 

[Bryman]

Hi Fluff,

I went to link below your name, I don't have admin rights on the PC I can't install the tools being referenced;

SCHEDULING INSTRUCTIONS: * A * B *C *D *E *F

to #VALUE!

I wanted it to convert to:

*A
*B
*C
*D
*E
*F

 

[Fluff]

Using both codes here I get

SCHEDULING INSTRUCTIONS:
* A
* B
*C
*D
*E
*F