Cell Decomposition problem

**mehdoush** · 02-21-2009, 04:35 PM

hey Leith, hey everyone

i have a problem how to decompose the last cell, here's the table i'm using:

A | B | C | D
1 value value value M1/M2/M3
2
'
'
'
n value value value S2/S4/S5

i want it to look like this :

A | B | C | D | E | F
1 value value value M1 M2 M3
2
'
'
'
n value value value S2 S4 S5

need help plz

thanks in advance

**JBeaucaire** · 02-21-2009, 05:19 PM

Highlight the cell and select Data > Text To Columns > Delimited

Click on [x] Space and [x] Other / and [x] treat consecutive delimiters as one

...and see if that gets you what you want.

**Leith Ross** · 02-21-2009, 07:26 PM

Hello mehdoush,

This macro will start with cell "D1" on worksheet "Sheet1" and continue down to the last entry in column "D". Cell data that is separated by a forward slashes will be place into the next successive column in the same row. The worksheet and range are marked in red if you need to change either one.

Sub ExpandData()

  Dim Cell As Range
  Dim Data As Variant
  Dim Rng As Range
  Dim RngEnd As Range
  Dim RngStart As Range
  Dim Wks As Worksheet
  
    Set Rng = Worksheets("Sheet1").Range("D1")
    Set Wks = Rng.Parent
    
      With Wks
        Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp)
        Set RngEnd = IIf(RngEnd.Row < Rng.Row, Rng, RngEnd)
        Set Rng = .Range(Rng, RngEnd)
      End With
      
      For Each Cell In Rng
        Data = Split(Cell, "/")
        For I = 0 To UBound(Data)
          Cell.Offset(0, I) = Data(I)
        Next I
     Next Cell
      
End Sub