Substitue, Clean, Trim Data Issues

[Cote06786]

I have not been successful with the following text cleaning adventure. I am trying to replace the " | " with a carriage return and remove all prevailing/trailing |. I have included an example so that yu can see the input and output. If you wish to see more data I can post it.

Example of text in cell A1:

| | | | 3IA11 - Develop and Receive approval for the Cutover Plan RO1.05 | | | | | | | | | | | | | | | | RO4.BP DPP - Begin to Measure, Display and Report Global Standard Business Planning Metrics and Targets RO4.09 | | | | | | | RO4.BP ECP - Begin to Measure, Display and Report Global Standard Business Planning Metrics and Targets RO4.16 | | | | | | RO4.TR - T-24 through T-12 Readiness Activities RO4.22 | | | | | | | | | | | | | | | | | | | </SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

Output desired:

3IA11 - Develop and Receive approval for the Cutover Plan RO1.05 RO4.BP DPP - Begin to Measure, Display and Report Global Standard Business Planning Metrics and Targets RO4.09 RO4.BP ECP - Begin to Measure, Display and Report Global Standard Business Planning Metrics and Targets RO4.16 RO4.TR - T-24 through T-12 Readiness Activities RO4.22</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

Thanks for your help!

 

[Rick Rothstein]

See if this macro does what you want (data is assumed to be in Column A)...

Sub ReplaceVerticalBarsWithLineFeeds()
  Dim Cell As Range, vNum As Variant, Txt As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Txt = Cell.Value
    For Each vNum In Array(121, 13, 5, 3, 3, 2)
      Txt = Replace(Txt, "| |", "|")
    Next
    Txt = Replace(Replace(Txt, "| ", vbLf), " |", vbLf)
    If Right(Txt, 1) = vbLf Then Txt = Left(Txt, Len(Txt) - 1)
    Cell.Value = Txt
  Next
End Sub

 

[Cote06786]

Thanks for the quick response. It does almost everything I am looking for except the carriage returns and I would like it for entire sheet.


Example of the data:
'Training (1) RO1.01
DCP EST Test - Conditional Acceptance Criteria RO1.b


3IA6 - Refine To Be Planning Processes RO1.05















RO4.BP DPP - Conduct Baseline Readiness Assessment RO4.09






RO4.BP ECP - Conduct Baseline Readiness Assessment RO4.16

 

[Rick Rothstein]

I am sorry, but I do not understand the example in your last message. Are you saying you want multiple Line Feeds (that is what separates lines of text within a cell, not Carriage Returns) contrary to what your original message showed you wanted?

 

[Cote06786]

Sorry for the confusion, I am looking for something to get rid of all the carriage returns except between the data. I get the data separated with Carriage return after carriage return. Then data the carriage return. Plus it has trailing Carriage returns. The example in the previous post (as my example). Below is what I am looking for.

Training (1) RO1.01
DCP EST Test - Conditional Acceptance Criteria RO1.b
3IA6 - Refine To Be Planning Processes RO1.05
RO4.BP DPP - Conduct Baseline Readiness Assessment RO4.09
RO4.BP ECP - Conduct Baseline Readiness Assessment RO4.16

 

[Rick Rothstein]

Sorry for the confusion, I am looking for something to get rid of all the carriage returns except between the data. I get the data separated with Carriage return after carriage return. Then data the carriage return. Plus it has trailing Carriage returns. The example in the previous post (as my example). Below is what I am looking for.

Training (1) RO1.01
DCP EST Test - Conditional Acceptance Criteria RO1.b
3IA6 - Refine To Be Planning Processes RO1.05
RO4.BP DPP - Conduct Baseline Readiness Assessment RO4.09
RO4.BP ECP - Conduct Baseline Readiness Assessment RO4.16

This code is probably longer than actually needed, but it should work against all combinations of vertical bars (with or without adjacent spaces), Line Feeds and/or Carriage Returns...

Sub ReplaceVerticalBarsWithLineFeeds()
  Dim Cell As Range, vNum As Variant, Txt As String
  For Each Cell In Range("A1", Cells(Rows.Count, "A").End(xlUp))
    Txt = Replace(Replace(Cell.Value, vbCr, "|"), vbLf, "|")
    For Each vNum In Array(121, 13, 5, 3, 3, 2)
      Txt = Replace(Txt, "| ", "|")
    Next
    For Each vNum In Array(121, 13, 5, 3, 3, 2)
      Txt = Replace(Txt, "||", "|")
    Next
    Txt = Replace(Txt, "|", vbLf)
    If Right(Txt, 1) = vbLf Then Txt = Left(Txt, Len(Txt) - 1)
    If Left(Txt, 1) = vbLf Then Txt = Mid(Txt, 2)
    Cell.Value = Txt
  Next
End Sub

 

[Cote06786]

Thanks for all the help Rick

 

[Cote06786]

One more thing I am looking for in the above code. Currently if the text starts with " | " it replaces it with (')(carriage return) (space). Is it possible to remove this at the beginning without effecting the cells in which the data is perfect?