Splitting one single cell into pieces

  • Thread starter Thread starter Mr_Huang
  • Start date Start date
M

Mr_Huang

I have a string cell contained different values which is separated by
a carriage return (?)

like this:

domain/user1
domain/user2
pc/user1
pc/user2

how can I use formula to
1. split the single cell into multiple cells (4 different cells)
2. place a count to do a loop to duplicate other cells to the newly
created lines
thanks
huang
 
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

.Cells(i, "A").TextToColumns _
Destination:=.Cells(i, "A"), _
Other:=True, OtherChar:=Chr(10), FieldInfo:=Array(1, 1)
Next i

End With

With Application

.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Mr_Huang said:
how can I use formula to
1. split the single cell into multiple cells (4 different cells)
Click to expand...

If you are happy with putting them in horizontally oriented cells, I
would recommend Excel's TextToColumns method:

Range ("A1").Value = "a" & vbCr & "b" & vbCr & "c" & vbCr & "d"
Range ("A1").TextToColumns Destination:=Range ("B1"),
DataType:=xlDelimited, Other:=True, OtherChar:=vbCr

This will split your single cell into multiple cells, but horizontally -
e.g.

A B C D E
1 a a b c d
b
c
d
2
3

If you want it vertically arranged, you will have to do it manually in
code I think.

Just created a function for you, see below.

HTH,

Lars

' Function: TextToRows
' Version: 2008-08-13
' Purpose: split a the delimiter-separated cells in a column into
' separate rows
' Example Usage: TextToRows (1, 2, 20, 45, ";")
Public Sub TextToRows(sourceCol As Long, _
Optional destCol As Long = -1, _
Optional fromRow As Long = 1, _
Optional toRow As Long = -1, _
Optional delimiter As String = vbCr)

Dim sourceRow As Long, destRow As Long
Dim rowStart As Long, rowEnd As Long
Dim sourceRowValue As String, rowValue As String

If (destCol = sourceCol) Then
MsgBox "Can not use source column as destination for " _
& "converted data!", vbCritical, "conversion failure"
End If

If (destCol = -1) Then
destCol = sourceCol + 1 ' default: write into next column
End If
If (toRow = -1) Then
' determine the highest used row number in the source column
toRow = Columns(sourceCol).Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).row
End If

destRow = fromRow
For sourceRow = fromRow To toRow
sourceRowValue = Cells(sourceRow, sourceCol).value
rowStart = 1 ' start searching for delimiter at first character
Do
' find next delimiter
rowEnd = InStr(rowStart, sourceRowValue, delimiter)

If (rowEnd = 0) Then ' no delimiter found
' get remaining cell data
rowValue = Mid(sourceRowValue, rowStart)
Else
' get string between delimiters
rowValue = Mid(sourceRowValue, rowStart, _
rowEnd - rowStart)
End If
Cells(destRow, destCol).value = rowValue
destRow = destRow + 1
rowStart = rowEnd + 1 ' reposition rowStart behind delimiter
Loop Until (rowEnd = 0 Or rowStart > Len(sourceRowValue))
Next sourceRow
End Sub
 
Sorry Bob,

I'm pretty new to vb for excel,

Further illustration:

A B C D
1 a Apple 12 as01
b
c
d
2 e Orange 80 os23
f
g
h
3 i Grape 200 gs44
j
k
l
and so on.
where "column A" will contain multiple value in a single cell, want to
split the single cell into multiple cells and duplicate the other cell
to the following lines,

Result:
A B C D
1 a Apple 12 as01
2 b Apple 12 as01
3 c Apple 12 as01
4 d Apple 12 as01
5 e Orange 80 os23
6 f Orange 80 os23
7 g Orange 80 os23
8 h Orange 80 os23
9 i Grape 200 gs44
10 j Grape 200 gs44
11 k Grape 200 gs44
12 l Grape 200 gs44

Is it possible to do so?

What is the "A" in LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row?
How can I run this to my excel worksheet? Place the cursor in A1, the
first cell contains multiple values?
tia,
Huang
 
thank you for your help,
however, i'm pretty new to vb in excel.
How can I run this formula? Cannot find this in the run menu of
Microsoft VB -> Marco dialogs.

Further illustration:

A B C D
1 a Apple 12 as01
b
c
d
2 e Orange 80 os23
f
g
h
3 i Grape 200 gs44
j
k
l
and so on.
where "column A" will contain multiple value in a single cell, want to
split the single cell into multiple cells and duplicate the other cell
to the following lines,

Result:
A B C D
1 a Apple 12 as01
2 b Apple 12 as01
3 c Apple 12 as01
4 d Apple 12 as01
5 e Orange 80 os23
6 f Orange 80 os23
7 g Orange 80 os23
8 h Orange 80 os23
9 i Grape 200 gs44
10 j Grape 200 gs44
11 k Grape 200 gs44
12 l Grape 200 gs44

is it possible?
How can I run this to my excel worksheet?
or is it a formula ?
tia,
Huang
 
Mr_Huang said:
thank you for your help,
however, i'm pretty new to vb in excel.
How can I run this formula? Cannot find this in the run menu of
Microsoft VB -> Marco dialogs.
Click to expand...

It's not a formula, it's a visual basic (for applications) procedure,
you need to enter the VBA editor:
Tools -> Macro -> Visual Basic Editor

On the upper left side of the window that opens, you'll see a project
explorer window. In that, right click and select Insert -> Module.

On the properties explorer on the lower left, choose a name to your
liking for the module.

Now paste the code I posted before into the editor window on the right side.
When you want to call that code from an excel sheet, you could for
example create a button on your sheet, and link the function with that,
with the desired parameters:
View -> Toolbars -> Control Toolbox (I don't have an english office
available atm, the menu names might not be 100% precise). From there,
select a command button. Double click it to enter the OnClick event
procedure stub. In that procedure, between the two lines that Excel
pre-created for you, just enter the call to the TextToRows function, for
example
TextToRows 1, 2, 1, 500, vbCr
(convert column 1, put values into column 2, read data from row 1 to row
500, look for carriage return (vbCr) as delimiter in the source values.)

Then, all you need to do in your sheet, is click on the button and voilá
- the text in column 1 is put into multiple rows in column 2.

You could also get the parameters for the function from cells in your
excel sheet, e.g.

TextToRows Range("E1"), Range("F1"), Range("G1"), Range("H1"), vbCr

HTH,

Lars
 
Back
Top