Interop.Excel Split and Copy

[Guest]

I'm trying to create a small plugin for Excel that will take a column (the
current selected column) and split the text into seperate columns based on
spaces.

I have the code to select the current column (although it is taking the
entire column, not the used range, any suggestions on that as well would be
nice)

Code Snippet

if (Application.ActiveWindow.Selection is Excel.Range)
{
Application.ScreenUpdating = false;
Excel.Range range = Application.ActiveWindow.Selection as Excel.Range;
range = range.EntireColumn;

}


I'm not exactly sure how to proceed from here. Below is a primitive example
of what I'd like to see.


Before
|ColA |
|Foo Bar |
|smarmy |
|Split Me|


After

|ColA |ColB |
|--------|--------|
|Foo |Bar |
|smarmy | |
|Split |Me |

 

[Leith Ross]

I'm trying to create a small plugin for Excel that will take a column (the
current selected column) and split the text into seperate columns based on
spaces.

I have the code to select the current column (although it is taking the
entire column, not the used range, any suggestions on that as well would be
nice)

Code Snippet

if (Application.ActiveWindow.Selection is Excel.Range)
{
Application.ScreenUpdating = false;
Excel.Range range = Application.ActiveWindow.Selection as Excel.Range;
range = range.EntireColumn;

}

I'm not exactly sure how to proceed from here. Below is a primitive example
of what I'd like to see.

Before
|ColA |
|Foo Bar |
|smarmy |
|Split Me|

After

|ColA |ColB |
|--------|--------|
|Foo |Bar |
|smarmy | |
|Split |Me |

Hello Krayakin,

Here is the macro to split the data into columns. It automatically
finds the last entry in column "A". It also will not go beyond the
maximum number of columns on the worksheet.

Sub SplitIntoColumns()

Dim C As Long, R As Long
Dim ColCount As Long, LastRow As Long
Dim Cols As Variant

With ActiveSheet
ColCount = .Columns.Count
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

For R = 1 To LastRow
Cols = Split(Cells(R, "A"), " ")
LastCol = UBound(Cols)
If LastCol > ColCount Then LastCol = ColCount
For C = 0 To LastCol
Cells(R, C + 1).Value = Cols(C)
Next C
End If
Next R

End Sub

Sincerely,
Leith Ross

 

[Ron Rosenfeld]

I'm trying to create a small plugin for Excel that will take a column (the
current selected column) and split the text into seperate columns based on
spaces.

I have the code to select the current column (although it is taking the
entire column, not the used range, any suggestions on that as well would be
nice)

Code Snippet

if (Application.ActiveWindow.Selection is Excel.Range)
{
Application.ScreenUpdating = false;
Excel.Range range = Application.ActiveWindow.Selection as Excel.Range;
range = range.EntireColumn;

}


I'm not exactly sure how to proceed from here. Below is a primitive example
of what I'd like to see.


Before
|ColA |
|Foo Bar |
|smarmy |
|Split Me|


After

|ColA |ColB |
|--------|--------|
|Foo |Bar |
|smarmy | |
|Split |Me |

I would suggest you record a macro and use the data/text to columns tool with
space as the delimiter for your "split" routine.

For expanding your range, you can use the CurrentRegion property of some cell
or of Selection.
--ron

 

[Guest]

This is how I would do it in VBA. Hopefully it gives you an idea and you can
come up with a C# equivalent.

Sub test()
Dim arr As Variant
Dim rng As Range
Dim c As Range
Dim i As Integer

Set rng = ActiveSheet.Range("A:A")

For Each c In rng
If c.Value = "" Then Exit For

arr = Split(c.Value, " ")
For i = 0 To UBound(arr)
c.Offset(0, i + 1).Value = arr(i)
Next i
Next c

End Sub

 

[Guest]

Here's the solution I ended up using. Not sure why this didn't come to me
earlier. For some reason I just couldn't wrap my head around it properly.

Excel.Worksheet sheet = Application.ActiveSheet as
Excel.Worksheet;
Excel.Range newRange;
Excel.Range range = Application.ActiveWindow.Selection as
Excel.Range;

long origCol = 0;
long newCol = 0;
string addr;
try
{
if (Application.ActiveWindow.Selection is Excel.Range)
{
Application.ScreenUpdating = false;
range = range.EntireColumn;
if (range.Columns.Count > 1)
{
MessageBox.Show("Cannot split more then one column
at a time.");
Application.ScreenUpdating = true;
return;
}
range.Copy(missing);

range.Insert(Excel.XlInsertShiftDirection.xlShiftToRight, missing);
System.Windows.Forms.Clipboard.Clear();

//get the address of the new cell
addr = range.get_Address(missing, missing,
Excel.XlReferenceStyle.xlR1C1, missing, missing);
addr = addr.Substring(addr.IndexOf('C') + 1);
newCol = long.Parse(addr);
origCol = newCol - 1;

//get the new column
newRange = sheet.Columns[newCol, missing] as Excel.Range;
range = sheet.Columns[origCol , missing] as Excel.Range;
//iterate through the rows and copy from one column to
the new one.
// have to start at 2 because the rows are 1 based index

string[] tmp;
for (int r = 2; r <= sheet.UsedRange.Rows.Count; r++)
{
if (((Excel.Range)range.Rows[r, missing]).Value2 !=
null)
{
tmp = ((Excel.Range)range.Rows[r,
missing]).Value2.ToString().Split(new char[] {' '},2);
((Excel.Range)range.Rows[r, missing]).Value2 =
tmp[0];
if (tmp.Length > 1)
((Excel.Range)newRange.Rows[r,
missing]).Value2 = tmp[1];
else
((Excel.Range)newRange.Rows[r,
missing]).Value2 = null;
}
}
}
}
catch (Exception ex)
{
ThisAddIn.Log.log("Error while splitting columns" +
Environment.NewLine + ex.ToString(), ex, Logger.LEVEL_ERROR);
}
finally
{
Application.ScreenUpdating = true;
}