split 1 cell to next consecutive cells

congokin

Board Regular
Joined
Oct 31, 2010
Messages
74
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
 
Last edited:

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,693
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Maybe you could post a better sample ??
 

congokin

Board Regular
Joined
Oct 31, 2010
Messages
74
before
Excel Workbook
A
1je vais au marche
2je mange du pain francais
3je parle avec mes amis d'ecole
4je descend chez le grand epicier
Sheet1
Excel Workbook
ABCDEF
1je vais au marcheaumarche
2je mange du pain francaisdupainfrancais
3je parle avec mes amis d'ecoleavecmesamisdecole
4je descend chez le grand epicierchezlegrandepicier
Excel 2007 after Sheet1
Excel 2007
i hope this will help
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,693
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Board Regular
Joined
Oct 31, 2010
Messages
74

ADVERTISEMENT

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

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
congokin,


Sample data before the macro:


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





After the macro:


Excel Workbook
ABCDE
1je vais au marcheaumarche
2je mange du pain francaisdupainfrancais
3je parle avec mes amis d'ecoleavecmesamisd'ecole
4je descend chez le grand epicierchezlegrandepicier
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.


Code:
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

Board Regular
Joined
Oct 31, 2010
Messages
74

ADVERTISEMENT

this macro works great...thanks
But i have a slight modification to make...:rolleyes:
Excel Workbook
A
1je vais au pays
2je vais dormir chez mes grands parents
3la veritable cle se trouve dans le marche a puces
4je suis entoure de mes amis
5je pense a toi
Sheet2
Excel 2007
after
Excel Workbook
ABCDEF
1je vais au paysaupays
2je vais dormir chez mes grands parentschezmesgrandsparents
3la veritable cle se trouve dans le marche a pucesdanslemarcheapuces
4je suis entoure de mes amisdemesamis
5je pense a toiatoi
Sheet2
Excel 2007

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

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
congokin,


New sample data before the macro:


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





After the macro:


Excel Workbook
ABCDE
1je vais au payspays
2je vais dormir chez mes grands parentsmesgrandsparents
3la veritable cle se trouve dans le marche a puceslemarcheapuces
4je suis entoure de mes amismesamis
5je pense a toitoi
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.


Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top