Split cells based on specific text which may occur multiple times in cell

[charlie_580]

Hi

I wonder if anyone can help?

I need to split multiple cells by code, however the code may occur within the original cell more than once. and example of my original cell has the following:

D11|1|2|text|numbers|text|numbers|text|numbers|text|numbers||||||||||||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numberstext|numbers|text|numbers||| |text|numbers|text|555|D11|32232|text|numbers|text|numbers||||text|numbers|text|3442 F12|4332|text|numbers|text|numbers|text|numbers|text|numbers|||||5353 D11||text|numbers|text|numbers||||| D11||text|numbers|text|numbers| |text|numbers|text|numbers|text|numbers|text|numbers|F17 text|numbers|text|numbers|text|numbers |text|numbers|text|numbers|text|numbers D11||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers| D11||text|numbers|text|numbers D11||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|D40|numbers|text|numbers D11|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numbers

I need the text extracted from the first code up to the second code and so on. My first cell of extracted data would be
D11|1|2|text|numbers|text|numbers|text|numbers|text|numbers||||||||||||text|numbers|text|numbers|text|numbers|text|numbers|text|numbers|text|numberstext|numbers|text|numbers||| |text|numbers|text|555|

The second would be
D11|32232|text|numbers|text|numbers||||text|numbers|text|3442

and the third
F12|4332|text|numbers|text|numbers|text|numbers|text|numbers|||||5353
and so on.

Once extracted, I would like to then insert the number of rows required for each extracted cell and list them all in the one column.
If I don't make sense, or if anyone can help, let me know.
Thanks

 

[charlie_580]

Does this macro work correctly with your data...

Sub SplitAndRearrange()
  Dim R As Long, V As Variant, Data As Variant, Arr As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  For R = 1 To UBound(Data)
    For Each V In Array("| F12 |", "| F17 |", "| F18 |", "| F19 |", "| F20 |", "| D04 |", "| D11 |", "| D20 |", "| 51 |", "| 63 |", "| 65 |", "| 51;", "| 63;", "| 65;")
      Data(R, 1) = Replace(Data(R, 1), V, Chr(1) & V)
    Next
    Arr = Split(Data(R, 1), Chr(1) & "| ")
    Cells(Rows.Count, "B").End(xlUp).Offset(1).Resize(1 + UBound(Arr)) = Application.Transpose(Arr)
  Next
End Sub

I can't get this to work. My data is in column P. I didn't want to mess with the formula. Thank you

 

[rlv01]

This works better. However there are a few cases of codes not being picked up meaning there are 2 sets of codes within the one cell after extraction.

Keep in mind that I only know what you tell me, and in an earlier post in response to questioning you said (emphasis mine):

All the error codes are in the format space bar space error code space bar space (eg " | D11 | ").

With the exception of the very first one, which has no bar. The code I posted relies on that being true. However your new sample contains instances that do not conform to your rule:

|TEXT F19|

So it is unsurprising that codes which do not conform to what you described will be missed. There is an old phrase from the early days of programming: "Garbage In Garbage Out" or "GIGO". I wish you well, but I think I'm done here.

 

[Rick Rothstein]

I can't get this to work. My data is in column P. I didn't want to mess with the formula. Thank you

I do not see where in any of your previous messages that you told us where your data was located, so the best I could do was guess... I guessed Column A. Here is my code revised to look for your data in Column P and put out the split apart data to Column Q (again, a guess on my part because you never said where the output should go to).

Sub SplitAndRearrange()
  Dim R As Long, V As Variant, Data As Variant, Arr As Variant
  Data = Range("P1", Cells(Rows.Count, "P").End(xlUp)).Value
  For R = 1 To UBound(Data)
    For Each V In Array("| F12 |", "| F17 |", "| F18 |", "| F19 |", "| F20 |", "| D04 |", "| D11 |", "| D20 |", "| 51 |", "| 63 |", "| 65 |", "| 51;", "| 63;", "| 65;")
      Data(R, 1) = Replace(Data(R, 1), V, Chr(1) & V)
    Next
    Arr = Split(Data(R, 1), Chr(1) & "| ")
    Cells(Rows.Count, "Q").End(xlUp).Offset(1).Resize(1 + UBound(Arr)) = Application.Transpose(Arr)
  Next
End Sub

NOTE
----------------------
This code assumes what you told me in Message # is correct... the codes have a bar and then a space in front of them and a space then bar behind them. If this is not always the case, then my code will not work.

 

[charlie_580]

Keep in mind that I only know what you tell me, and in an earlier post in response to questioning you said (emphasis mine):


With the exception of the very first one, which has no bar. The code I posted relies on that being true. However your new sample contains instances that do not conform to your rule:

|TEXT F19|

So it is unsurprising that codes which do not conform to what you described will be missed. There is an old phrase from the early days of programming: "Garbage In Garbage Out" or "GIGO". I wish you well, but I think I'm done here.

Thank you for your help. It is much appreciated. I am afraid I missed the ones like this and you have helped me identify them. Unfortunately, having looked at these instances, I can't say how many cases there are.

All the best.

 

[charlie_580]

I do not see where in any of your previous messages that you told us where your data was located, so the best I could do was guess... I guessed Column A. Here is my code revised to look for your data in Column P and put out the split apart data to Column Q (again, a guess on my part because you never said where the output should go to).

Sub SplitAndRearrange()
  Dim R As Long, V As Variant, Data As Variant, Arr As Variant
  Data = Range("P1", Cells(Rows.Count, "P").End(xlUp)).Value
  For R = 1 To UBound(Data)
    For Each V In Array("| F12 |", "| F17 |", "| F18 |", "| F19 |", "| F20 |", "| D04 |", "| D11 |", "| D20 |", "| 51 |", "| 63 |", "| 65 |", "| 51;", "| 63;", "| 65;")
      Data(R, 1) = Replace(Data(R, 1), V, Chr(1) & V)
    Next
    Arr = Split(Data(R, 1), Chr(1) & "| ")
    Cells(Rows.Count, "Q").End(xlUp).Offset(1).Resize(1 + UBound(Arr)) = Application.Transpose(Arr)
  Next
End Sub

NOTE
----------------------
This code assumes what you told me in Message # is correct... the codes have a bar and then a space in front of them and a space then bar behind them. If this is not always the case, then my code will not work.

Thanks so much for your help.

Unfortunately, with the use of the codes given, it appears I was mistaken with the format of the data. I apologise for wasting evryones time.

All help recieved is greatly appreciated.

All the best.

 

[Rick Rothstein]

We still may be able to help you. If you do not mind losing the space after the code when one is present, then this might work for you. It assumes there is always a bar before and after the code whether there is a space between the bar and the code or not. Give it a try and let me know...

Sub SplitAndRearrange()
  Dim R As Long, V As Variant, Data As Variant, Arr As Variant
  Data = Range("P1", Cells(Rows.Count, "P").End(xlUp)).Value
  For R = 1 To UBound(Data)
    For Each V In Array("F12", "F17", "F18", "F19", "F20", "D04", "D11", "D20", "51", "63", "65", "51;", "63;", "65;")
      Data(R, 1) = Replace(Replace(Replace(Data(R, 1), "| " & V, "|" & V), V & " |", V & "|"), "|" & V & "|", Chr(1) & V)
    Next
    Arr = Split(Data(R, 1), Chr(1) & "| ")
    Cells(Rows.Count, "Q").End(xlUp).Offset(1).Resize(1 + UBound(Arr)) = Application.Transpose(Arr)
  Next
End Sub