Split Text /Split text to rows

[Stefan_Sand]

Hi all,

I attached a workbook, where You can see my dilemma. I have to split my data; the original base is a projectplan, where all the deliveries are in the notices field.
When i import the plan into excel, i have the text in column k in a flat form. But it should be like a pivot list.
In order to all the deliveries in Column K (split through : and :â–º) there should be the corresponding wbs number and the task to each content of the cell in column k.
can you please help me to not solve it by hand.... (there are more than 600 tasks)...

maybe someone of you can handle the problem?
thanks in advance,
stef

[HansV]

Could you provide a small sample of what you'd like the output to look like?

[Stefan_Sand]

hi hans,
the result is shown in the workbook from row 8 to row 15, where i split the data of task 2 - PSP nr. : 1.6.10
regards
stef

[HansV]

The contents of cell K4 is the long text

BEMERKUNG: Integration der SystemeAP-INHALT:â–º FW Rulesâ–º Online Syncâ–º StatEngineNICHT-AP-INHALT-AP-ERGEBNISSE:â–º Integrationsdokumentâ–º System ist Ready for TestingLEISTUNGSFORTSCHRITTSMESSUNG:-QUELLEN / REFERENZEN:

I have several questions about the way you split the text:

1. There is no separator between "Systeme" and "AP-INHALT" etc. How should a dumb computer that doesn't know German determine where to split the text?
2. What happened to "NICHT-AP-INHALT-" in "NICHT-AP-INHALT-AP-ERGEBNISSE"?
3. What happened to "LEISTUNGSFORTSCHRITTSMESSUNG:-QUELLEN / REFERENZEN:"?

Keep in mind that an automated or semi-automated solution can't rely on understanding the text.

[Stefan_Sand]

as i first described, the separator could be : or :â–º
not in every cell there is a text like "AP-Inhalt" and "NICHT-AP-INHALT"

[HansV]

Please answer the questions from my previous reply.

[Stefan_Sand]

1. the only seperators are after SystemeAP-INHALT :â–º
2. if there is no seperator like :â–º - the text should remain in the flatform
3. this point can be kicked out or remain in flat form

please see the complete xls file with all the text.

the original plan was an excel file; then we hat to put all the deliveries into a ms project plan and now, after giving up all the structure i should re-import it to excel....

[HansV]

If :â–º is the only delimiter, the text "BEMERKUNG: Integration der SystemeAP-INHALT" would remain together in one cell. Yet in your example it is split into two cells, one with "BEMERKUNG: Integration der Systeme" and the other with "AP-INHALT".
Similarly, the text "StatEngineNICHT-AP-INHALT-AP-ERGEBNISSE" has mysteriously been transformed into "StatEngine" and "AP-ERGEBNISSE", where the text "NICHT-AP-INHALT-" has completely disappeared.

Unless you can specify exact criteria, it will be impossible to reconstruct the sample you provided.

[Stefan_Sand]

You are right Hans,
I split the text only by hand as i wanted the "best" result - as i only have to get out the deliveries per task, the best delimiter is: the â–º can stay with the text
regards
stefan

[HansV]

Using : as delimiter won't work - cell K5 contains many instances of â–º without :
Would it be OK if text such as

► Betriebsführungshandbuch (BFHB)► Hardware Architektur► Install Guide Dev► Interface Beschreibungen► MOP (Minutes of Procedure)► Netzplan► Prozessdokument► Spezifikation IVR Mediator► Spezifikation Mehrfachkontakte (Funktional)► Spezifikation SpeechConnectorNICHT-AP-INHALT-AP-ERGEBNISSE

stays together? There isn't a single : in there.

[Stefan_Sand]

damn, you are right... oh my go...
in this case it would be â–º....
i hate this work

[HansV]

Unfortunately, VBA can't handle â–ºsince it is a Unicode character. So this will have to be a two-step process:

1) In Excel:
- Select the â–º character from any cell in column K. Take care not to select any character before or after it.
- Press Ctrl+C to copy it.
- Select column K.
- In the Home tab of the ribbon, click on Find&Select, and select Replace... from the dropdown menu (or press Ctrl+H)
- Paste the â–º into the Find what box.
- Type @ into the Replace with box.
- Click Replace All.
- Close the Replace dialog.

2) In the Visual Basic Editor:
- Select Insert | Module.
- Copy/paste the following macro into the module:

Code: Select all

Sub Transform()
  Const strCol = "K"
  Const strFirst = "B"
  Const strLast = "G"
  Dim r As Long
  Dim m As Long
  Dim i As Long
  Dim strValue As String
  Dim arrParts As Variant
  Application.ScreenUpdating = False
  ' Lasty used row
  m = Range(strCol & Rows.Count).End(xlUp).Row
  ' Loop backwards through the cells
  For r = m To 3 Step -1
    ' Get value of Notizen
    strValue = Range(strCol & r).Value
    ' Remove text to be ignored
    strValue = Replace(strValue, "LEISTUNGSFORTSCHRITTSMESSUNG:-QUELLEN / REFERENZEN:", "")
    ' Split
    arrParts = Split(strValue, "@")
    ' Loop backwards through the parts
    For i = UBound(arrParts) To 1 Step -1
      ' Insert a new row
      Range(strCol & (r + 1)).EntireRow.Insert
      ' Copy columns B:G
      Range(strFirst & r & ":" & strLast & (r + 1)).FillDown
      ' Fill column K
      Range(strCol & (r + 1)).Value = Trim(arrParts(i))
    Next i
    ' Handle the first part separately
    Range(strCol & r).Value = Trim(arrParts(0))
  Next r
  Application.ScreenUpdating = True
End Sub

- Run the code.

You will have to perform additional cleanup manually; perhaps you can use Find/Replace for some of it.
If you want to keep the macro, don't forget to save the workbook as a macro-enabled workbook (.xlsm).

[Stefan_Sand]

ok, i get error nr 9

Range(strCol & r).Value = Trim(arrParts(0))

[HansV]

Are there blank cells within the used range in column K? That wasn't the case in your sample workbook.

If so, replace the offending line with

Code: Select all

    If UBound(arrParts) >= 0 Then
      Range(strCol & r).Value = Trim(arrParts(0))
    End If

[Stefan_Sand]

thank You so much, Hans!!!!