For Each c In rng.Cells If Not IsNumeric(c) Then ‘ Only if is not number sString = c.Value sNum = vbNullString For i = 1 To Len(sString) n = 0 On Error Resume Next n = ( _ Mid(sString, i, 1), arrNumbers, 0) On Error GoTo 0 If n 0 Then sNum = sNum & Mid(sString, i, 1) Else Exit For ‘ > stop loop <<< End If Next i c.Value = CInt(sNum) End If Next cĬhuck, here is optimized version Hope you will be satisfied. ‘ When the application be correct, then erase this command. Set rng = rng.Resize(, iColumns).SpecialCells( _ xlCellTypeConstants, 22) ‘ 22 = All values except numbers End With ‘rng.Select ‘ this instruction for your test only. Range(csCOLL & “65000”).End(xlUp)) ‘ Resize Used Range process only constants in all columns. ‘– ‘ Reference used Range in Column ‘A’ Set rng =. Columns(csCOLL).Column End If Next col ‘MsgBox iColumns ‘ this instruction for your test only. Columns ‘ Loop Columns If (col) = 0 Then Exit For ‘ > Else iColumns =. Find how many columns occupied iColumns =.
#EXCEL SPLIT CELLS INTO NEIGHBORING UPDATE#
For Each c In rng.Cells If Not IsNumeric(c) Then ‘ Only if is not number sString = c.Value sNum = vbNullString For i = 1 To Len(sString) n = 0 On Error Resume Next n = ( _ Mid(sString, i, 1), arrNumbers, 0) On Error GoTo 0 If n 0 Then sNum = sNum & Mid(sString, i, 1) Else Exit For ‘ > stop loop <<< End If Next i c.Value = CInt(sNum) End If Next cĬhuck, here is corrected solution: ‘– Sub BreakTextToColumns() ‘ Note: Table of broken data must be ‘ separated from all other data in the worksheet, ‘ with at least one blank column! ‘– Dim c As Range ‘ Represents a single cell Dim col As Range ‘ Represents a single column Dim rng As Range ‘ Represents a Range that contain break text Dim sString As String Dim sNum As String Dim bNext As Boolean Dim arrSplitText As Variant Dim arrNumbers As Variant Dim iColumns As Integer ‘ How many columns occupied Dim i As Long, j As Long, k As Long, n As Long Const gsCOMMA As String = “,” ‘ Comma Text delimiter Const csCOLL As String = “AD” ‘ Leading Column Letter (customize) ‘– Application.ScreenUpdating = False For Each c In ActiveSheet.Columns(csCOLL).SpecialCells( _ xlCellTypeConstants) If bNext Then bNext = True: GoTo LabNextCell ‘> End If arrSplitText = Split(c.Value, gsCOMMA) ‘ Update columns c.Resize(1, UBound(arrSplitText) + 1) = arrSplitText LabNextCell: Next c ‘– ‘ Extract Left Numbers only With ActiveSheet ‘ 1.Range(“A65000”).End(xlUp)) ‘ Resize Used Range process only constants in all columns. Columns ‘ Loop Columns If (col) = 0 Then Exit For ‘ > Else iColumns = iColumns + 1 End If Next col ‘MsgBox iColumns ‘ this instruction for your test only. Find how many columns occupied iColumns = 0 For Each col In. Here is completed procedure: ‘– Sub BreakTextToColumns() ‘ Note: Table of broken data must be ‘ separated from all other data in the worksheet, ‘ with at least one blank column! ‘– Dim c As Range ‘ Represents a single cell Dim col As Range ‘ Represents a single column Dim rng As Range ‘ Represents a Range that contain break text Dim sString As String Dim sNum As String Dim bNext As Boolean Dim arrSplitText As Variant Dim arrNumbers As Variant Dim iColumns As Integer ‘ How many columns occupied Dim i As Long, j As Long, k As Long, n As Long Const gsCOMMA As String = “,” ‘ Comma Text delimiter ‘– Application.ScreenUpdating = False For Each c In ActiveSheet.Columns(1).SpecialCells( _ xlCellTypeConstants) If bNext Then bNext = True: GoTo LabNextCell ‘> End If arrSplitText = Split(c.Value, gsCOMMA) ‘ Update columns c.Resize(1, UBound(arrSplitText) + 1) = arrSplitText LabNextCell: Next c ‘– ‘ Extract Left Numbers only With ActiveSheet ‘ 1. TextFileTextQualifier = xlTextQualifierDoubleQuote. With (Connection:= _ “TEXT C:Documents and SettingsAll UsersDocumentsfile.csv”, Destination:= _ Range(“A1”)). If that’s the case, try something like this... It sounds like you’re just importing a *.csv file, if I’m not mistaken and you should use the Data -> Import External Data -> Import Data from the menu (XL 2003), or something similar in another version.