Splitting the characters in the cell

C

Christopher Naveen

Hi

I have a cell which contains 300+ of email ID's seperated by commas, Pls
refer the example as shown below

A1
[email protected], [email protected], [email protected], [email protected], [email protected],
[email protected], [email protected], [email protected], ..........

Now i want to split the email ID's seperately as show below,

A1
[email protected],
A2
[email protected],
A3
[email protected],
A4
[email protected], like this. Can anyone help me how to do this in excel.

Also I want to know the cell contains 300+ email ID's which cannot be
spilted in the cols as excel permits max of 256 cells in cols wher i have
300+ email ID's. if i want to split the email ID's in row how can i do that?

Eagerly Waiting for ur reply.

Thanks in Advance!!!!!

-Christ.
 
S

ShaneDevenshire

Hi,

Select the cell and choose Data, Text to Columns, Delimited, Next, set the
delimiter to , (comma) and click finish. If there are more than 256 entries
this may cause a problem if you are running 2003 or earlier.
Highlight all the columns with addresses and choose copy, click an empty
cell and choose Edit, Paste Special, Transpose.

If there are really 300+ items highlight about half of them on the formula
bar and copy and paste those to a separate cell. The do the above steps
twice.
 
C

Christopher Naveen

Hi Shane,

Great!!!! Its working fine. Thanks a lot for ur quick response.

I want to know do we have any formula to check a few characters in cell and
calculate the value?

Ex :

I have a col and it contains some charatcers i want to check for a
particular character in a cell and if it is true i want to return some value
in the next col.

A1 B1
abc if(A1 contains "L", "YES", "NO")
cde if(A2 contains "L", "YES", "NO")
efg if(A3 contains "L", "YES", "NO")
ghi ...........
KJL ...........
MLM ...........
NLP ...........
OPP ...........
LLO ...........

Can u pls check and let me know how can we calculate this?.

Thanks !!!!!!!

-Chrsit
 
R

Ron Rosenfeld

Hi

I have a cell which contains 300+ of email ID's seperated by commas, Pls
refer the example as shown below

A1
[email protected], [email protected], [email protected], [email protected], [email protected],
[email protected], [email protected], [email protected], ..........

Now i want to split the email ID's seperately as show below,

A1
[email protected],
A2
[email protected],
A3
[email protected],
A4
[email protected], like this. Can anyone help me how to do this in excel.

Also I want to know the cell contains 300+ email ID's which cannot be
spilted in the cols as excel permits max of 256 cells in cols wher i have
300+ email ID's. if i want to split the email ID's in row how can i do that?

Eagerly Waiting for ur reply.

Thanks in Advance!!!!!

-Christ.
Click to expand...

You could use this VBA Macro to split the cell into rows. I started with A2,
so as to preserve your original data, but it could be modified to wipe out the
original entry.

To enter this, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explorer window, then Insert/Module and paste the
code below into the window that opens.

To use it, select the cell(s) you wish to split on comma. <alt-F8> opens the
macro dialog box, RUN the macro.

===========================================
Option Explicit
Sub SplitToRows()
Const Comma As String = ","
Dim c As Range
Dim i As Long
Dim sSplitArray() As String

For Each c In Selection
sSplitArray = Split(c.Value, Comma)

For i = 0 To UBound(sSplitArray)
c(i + 2, 1).Value = Replace(Trim(sSplitArray(i)), vbLf, "")
Next i
Next c
End Sub
=============================================

To split to columns, with some maximum number of columns, you can try this
macro:

==========================================
Sub SplitToColumns()
Const MaxCols As Long = 256 'set this to whatever or use inputbox
Const Comma As String = ","
Dim c As Range
Dim i As Long
Dim sSplitArray() As String

For Each c In Selection
sSplitArray = Split(c.Value, Comma)

For i = 0 To UBound(sSplitArray)
c(1 + Int(i / MaxCols), 2 + i Mod MaxCols).Value =
Replace(Trim(sSplitArray(i)), vbLf, "")
Next i
Next c
End Sub
=============================================
--ron
 
R

Ron Rosenfeld

To split to columns, with some maximum number of columns, you can try this
macro:

==========================================
Sub SplitToColumns()
Const MaxCols As Long = 256 'set this to whatever or use inputbox
Const Comma As String = ","
Dim c As Range
Dim i As Long
Dim sSplitArray() As String

For Each c In Selection
sSplitArray = Split(c.Value, Comma)

For i = 0 To UBound(sSplitArray)
c(1 + Int(i / MaxCols), 2 + i Mod MaxCols).Value =
Replace(Trim(sSplitArray(i)), vbLf, "")
Next i
Next c
End Sub
=============================================
--ron
Click to expand...

I noticed my newsreader wrapped a line in this second macro in the wrong spot.
So as to prevent that, I forced a line break appropriately:

=============================================
Sub SplitToColumns()
Const MaxCols As Long = 3 'set this to whatever or use inputbox
Const Comma As String = ","
Dim c As Range
Dim i As Long
Dim sSplitArray() As String

For Each c In Selection
sSplitArray = Split(c.Value, Comma)

For i = 0 To UBound(sSplitArray)
c(1 + Int(i / MaxCols), 2 + i Mod MaxCols).Value = _
Replace(Trim(sSplitArray(i)), vbLf, "")
Next i
Next c
End Sub
===========================================
--ron
 
Top