split 1 cell to next consecutive cells

[congokin]

i have a series of french sentences in colA and would like to split these sentences after these words "au, du, avec, chez"

Excel Workbook
	E
6
Sheet1

Excel 2007
Before:
cola
je vais au marche
je mange du pain francais
je parle ave\c mes amis d'ecole
je descend chez le grand epicier
after:
col B | col c | col D
au | marche
du | pain | francais
avec | mes | amis | d | ecole|
chez | le | grand | epicier

 

[Michael M]

Maybe you could post a better sample ??

 

[congokin]

before

Excel Workbook
	A
1	je vais au marche
2	je mange du pain francais
3	je parle avec mes amis d'ecole
4	je descend chez le grand epicier
Sheet1

Excel Workbook
	A	B	C	D	E	F
1	je vais au marche	au	marche
2	je mange du pain francais	du	pain	francais
3	je parle avec mes amis d'ecole	avec	mes	amis	d	ecole
4	je descend chez le grand epicier	chez	le	grand	epicier
Excel 2007 after Sheet1

Excel 2007
i hope this will help

 

[Michael M]

Have you considered using "Text to Columns" ??

So, in Excel 2007
Highlight the cells in the column / Data / Text To Columns
and use "fixed width" as the delimiter.

 

[congokin]

i have considered it but after using it i a have to go back and merge cells because...because each sentence is very different from the next one...
the only pattern are the words "au", "chez", "du"...

 

[hiker95]

congokin,


Sample data before the macro:

Excel Workbook
	A	B	C	D	E
1	je vais au marche
2	je mange du pain francais
3	je parle avec mes amis d'ecole
4	je descend chez le grand epicier
5
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	je vais au marche	au	marche
2	je mange du pain francais	du	pain	francais
3	je parle avec mes amis d'ecole	avec	mes	amis	d'ecole
4	je descend chez le grand epicier	chez	le	grand	epicier
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitColA()
' hiker95, 03/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=537225
Dim c As Range, Sp, a As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  Sp = Split(c, " ")
  For a = 2 To UBound(Sp)
    c.Offset(, a - 1) = Sp(a)
  Next a
Next c
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the SplitColA macro.

 

[congokin]

this macro works great...thanks
But i have a slight modification to make...

Excel Workbook
	A
1	je vais au pays
2	je vais dormir chez mes grands parents
3	la veritable cle se trouve dans le marche a puces
4	je suis entoure de mes amis
5	je pense a toi
Sheet2

Excel 2007
after

Excel Workbook
	A	B	C	D	E	F
1	je vais au pays	au	pays
2	je vais dormir chez mes grands parents	chez	mes	grands	parents
3	la veritable cle se trouve dans le marche a puces	dans	le	marche	a	puces
4	je suis entoure de mes amis	de	mes	amis
5	je pense a toi	a	toi
Sheet2

Excel 2007

Basically i only need words after the following "au", "chez", "dans", "de", "a"

 

[hiker95]

congokin,

Be back soon.

 

[hiker95]

congokin,


New sample data before the macro:

Excel Workbook
	A	B	C	D	E
1	je vais au pays
2	je vais dormir chez mes grands parents
3	la veritable cle se trouve dans le marche a puces
4	je suis entoure de mes amis
5	je pense a toi
6
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E
1	je vais au pays	pays
2	je vais dormir chez mes grands parents	mes	grands	parents
3	la veritable cle se trouve dans le marche a puces	le	marche	a	puces
4	je suis entoure de mes amis	mes	amis
5	je pense a toi	toi
6
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub SplitColA_V2()
' hiker95, 03/19/2011
' http://www.mrexcel.com/forum/showthread.php?t=537225
Dim c As Range, Sp, a As Long, Sp2
Dim Wary
Application.ScreenUpdating = False
Wary = Array(" au ", " chez ", " dans ", " de ", " a ")
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  For a = LBound(Wary) To UBound(Wary)
    If InStr(c, Wary(a)) > 0 Then
      Sp = Split(c, Wary(a))
      If InStr(Sp(1), " ") = 0 Then
        c.Offset(, 1) = Sp(1)
      Else
        Sp2 = Split(Sp(1), " ")
        c.Offset(, 1).Resize(, UBound(Sp2) + 1).Value = Sp2
      End If
      Exit For
    End If
  Next a
Next c
ActiveSheet.UsedRange.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

Then run the SplitColA_V2 macro.

 

[congokin]

wow...thanks a lot hiker95