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:


[TABLE="width: 483"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Example[/TD]
[TD]Desired Outcome[/TD]
[/TR]
[TR]
[TD]*First Line*Second Line*Third Line[/TD]
[TD]*First Line
*Second Line
*Third Line[/TD]
[/TR]
[TR]
[TD]*First Line
*Second Line*Third Line[/TD]
[TD]*First Line
*Second Line
*Third Line

[/TD]
[/TR]
</tbody>[/TABLE]


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?

 

[Bryman]

Thank you. You're right Fluff, there was a formatting issue with the extract, I extracted the data again from the database in a different format and it worked how I wanted to with one exception:

If I have say:
***A *B *C ****D

I was hoping to use the above macro to convert it to:
***A
*B
*C
****D

Thanks so much !

 

[Rick Rothstein]

Thank you. You're right Fluff, there was a formatting issue with the extract, I extracted the data again from the database in a different format and it worked how I wanted to with one exception:

If I have say:
***A *B *C ****D

I was hoping to use the above macro to convert it to:
***A
*B
*C
****D

In your first message, you had no spaces in front of the asterisk whereas now you are showing a space there... is it possible that there might be a space and also no space within your data set, or is it one or the other? Please clarify.

 

[Bryman]

Yes, reviewing my data it's inconsistent; sometimes there is a space and sometimes there is not. Sorry I did not account for that in my ask. I was trying to simplify it to make this post applicable to more users.

 

[Rick Rothstein]

Yes, reviewing my data it's inconsistent; sometimes there is a space and sometimes there is not. Sorry I did not account for that in my ask. I was trying to simplify it to make this post applicable to more users.

Does this macro work for you...

[table="width: 500"]
[tr]
	[td]Sub AddLFs()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Replace(Data(R, 1), vbLf, ""), " ", "")
    For X = Len(Data(R, 1)) To 2 Step -1
      If Mid(Data(R, 1), X - 1, 2) Like "[!*][*]" Then Data(R, 1) = Application.Replace(Data(R, 1), X, 0, vbLf)
    Next
  Next
  With Range("A1").Resize(UBound(Data))
    .Value = Data
    .WrapText = True
    .EntireColumn.ColumnWidth = 200
    .EntireColumn.AutoFit
    .EntireRow.RowHeight = 100
    .EntireRow.AutoFit
  End With
End Sub[/td]
[/tr]
[/table]

 

[Bryman]

Almost, but it trimmed out the spaces between words in sentences (that was my fault I didn't tell you I was working with sentences):

What it was before Macro:
SCHEDULING INSTRUCTIONS: * Schedule contrast studies at 1500 *Contrast Studies are only scheduled at 3pm. If patient requires more than one contrast study call department. * This is an open MRI * Schedule two business days out if insurance requires precertification. Medicare and Medicaid can be scheduled in the next available slot.

What it looked like After the Macro:
SCHEDULINGINSTRUCTIONS:
*Schedulecontraststudiesat1500
*ContrastStudiesareonlyscheduledat3pm.Ifpatientrequiresmorethanonecontraststudycalldepartment.
*ThisisanopenMRI
*Scheduletwobusinessdaysoutifinsurancerequiresprecertification.MedicareandMedicaidcanbescheduledinthenextavailableslot.

 

[Rick Rothstein]

Almost, but it trimmed out the spaces between words in sentences (that was my fault I didn't tell you I was working with sentences):

What it was before Macro:
SCHEDULING INSTRUCTIONS: * Schedule contrast studies at 1500 *Contrast Studies are only scheduled at 3pm. If patient requires more than one contrast study call department. * This is an open MRI * Schedule two business days out if insurance requires precertification. Medicare and Medicaid can be scheduled in the next available slot.

What it looked like After the Macro:
SCHEDULINGINSTRUCTIONS:
*Schedulecontraststudiesat1500
*ContrastStudiesareonlyscheduledat3pm.Ifpatientrequiresmorethanonecontraststudycalldepartment.
*ThisisanopenMRI
*Scheduletwobusinessdaysoutifinsurancerequiresprecertification.MedicareandMedicaidcanbescheduledinthenextavailableslot.

Will you ever have multiple adjacent asterisks or not (your above example does not show any)?

 

[Rick Rothstein]

Will you ever have multiple adjacent asterisks or not (your above example does not show any)?

Ignore my above question (for now) and see if this macro works for you...

[table="width: 500"]
[tr]
	[td]Sub AddLFs()
  Dim R As Long, X As Long, Data As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    Data(R, 1) = Replace(Data(R, 1), vbLf, "")
    For X = Len(Data(R, 1)) To 2 Step -1
      If Mid(Data(R, 1), X - 1, 2) Like "[!*][*]" Then Data(R, 1) = Application.Replace(Data(R, 1), X, 0, vbLf)
    Next
  Next
  With Range("A1").Resize(UBound(Data))
    .Value = Data
    .WrapText = True
    .EntireColumn.ColumnWidth = 200
    .EntireColumn.AutoFit
    .EntireRow.RowHeight = 100
    .EntireRow.AutoFit
  End With
End Sub[/td]
[/tr]
[/table]

 

[Bryman]

wow, that works wonderfully Rick! That will save me a lot of headaches thank you so much!