Split Text /Split text to rows
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Split Text /Split text to rows
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
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
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
Could you provide a small sample of what you'd like the output to look like?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
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
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
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
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:
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:
- 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?
- What happened to "NICHT-AP-INHALT-" in "NICHT-AP-INHALT-AP-ERGEBNISSE"?
- What happened to "LEISTUNGSFORTSCHRITTSMESSUNG:-QUELLEN / REFERENZEN:"?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
as i first described, the separator could be : or :â–º
not in every cell there is a text like "AP-Inhalt" and "NICHT-AP-INHALT"
not in every cell there is a text like "AP-Inhalt" and "NICHT-AP-INHALT"
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
Please answer the questions from my previous reply.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
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....
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....
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
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.
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
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
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
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
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.
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.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
damn, you are right... oh my go...
in this case it would be â–º....
i hate this work
in this case it would be â–º....
i hate this work
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
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:
- 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).
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
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).
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
ok, i get error nr 9
Range(strCol & r).Value = Trim(arrParts(0))
Range(strCol & r).Value = Trim(arrParts(0))
-
- Administrator
- Posts: 78790
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Split Text /Split text to rows
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
If so, replace the offending line with
Code: Select all
If UBound(arrParts) >= 0 Then
Range(strCol & r).Value = Trim(arrParts(0))
End If
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 415
- Joined: 29 Mar 2010, 11:50
- Location: Vienna, Austria
Re: Split Text /Split text to rows
thank You so much, Hans!!!!