About Us

[email protected]

On Tuesday, 10 December 2013 17:28:24 UTC, Claus Busch wrote:
Hi,

Am Tue, 10 Dec 2013 08:59:50 -0800 (PST) schrieb :

Thanks for this unfortunately it splits column A over 2 columns but but not original columns B and C. I showed 3 columns of data but sometimes there are up to 20 columns that need to be split if this makes a difference.

if TextToColumns doesn't work try:

Sub SplitString()

Dim LRow As Long

Dim LCol As Long

Dim i As Long

Dim Start As Integer

Dim rngC As Range

With ActiveSheet

LCol = .Cells(1, Columns.Count).End(xlToLeft).Column

For i = 2 To 2 * LCol Step 2

.Columns(i).Insert

LRow = .Cells(Rows.Count, i - 1).End(xlUp).Row

For Each rngC In .Range(.Cells(1, i - 1), .Cells(LRow, i - 1))

Start = InStrRev(rngC, " ")

rngC.Offset(, 1) = Trim(Mid(rngC, Start + 1, 99))

rngC = Trim(Left(rngC, Start - 1))

Next

Next

End With

End Sub

Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Thanks so much Claus - with a few edits this piece of code has been perfect and suits my needs perfectly.
Eddie

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
problem with test to columns feature identifying odd delimiting character	mark_jm via OfficeKB.com	Excel Discussion (Misc queries)	4	May 4th 10 11:22 AM
Un-Delimiting	caveman.savant	Excel Programming	2	April 30th 09 03:09 PM
Delimiting and Formatting	Delimiting and Formatting	Excel Discussion (Misc queries)	2	May 9th 07 07:26 PM
delimiting w/multiple delimiters?	NTaylor	Excel Discussion (Misc queries)	0	January 11th 06 04:22 PM
Pasting from clipboard-how to control text-to-columns delimiting	stebro	Excel Discussion (Misc queries)	1	June 15th 05 05:31 PM

Menu

About Us