Dela upp information i rader

May 27

James har några data i ett kalkylblad som finns i en serie av rader. En av kolumnerna i data inkluderar celler som har flera rader per cell. (Uppgifterna i cellen separerades i rader genom att trycka Alt + Enter mellan poster.) James vill dela dessa data till flera rader. Till exempel, om det fanns tre rader med data i en enda cell i raden, då data i denna cell bör delas ut i tre rader.

Excel innehåller ett praktiskt sätt att dela data till separata kolumner med hjälp av Text till kolumner verktyg. Detta kan användas för att dela upp data baserat på förekomsten av ASCII 10 tecken, vilket är vad Excel infogar när du trycker Alt + Enter. Problemet är att medan detta framgångsrikt delar upp data i separata kolumner, inte få det i separata rader, som James begärt.

Det innebär att lösningen på detta problem måste innefatta användning av ett makro. Ett tillvägagångssätt visas i följande kod. I detta exempel antar makro som du vill "expandera" allt i kalkylbladet, och att uppgifterna i kalkylbladet börjar i rad 1.

Sub CellSplitter1 ()
Dim Temp As Variant
Dim CTEXT As String
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim iColumn As Integer
Dim lNumCols som Long
Dim lNumRows som Long

iColumn = 4

Ställ wksSource = ActiveSheet
Ställ wksNew = Worksheets.Add

iTargetRow = 0
Med wksSource
lNumCols = .Range ("IV1"). End (xlToLeft) .Column
lNumRows = .Range ("A65536"). End (xlUp) .Row
För J = 1 Till lNumRows
CTEXT = .Cells (J, iColumn) .Value
Temp = Split (CTEXT, Chr (10))
För K = 0 Till UBound (Temp)
iTargetRow = iTargetRow + 1
För L = 1 till lNumCols
Om L <> iColumn Sedan
wksNew.Cells (iTargetRow, L) _
= .Cells (J, L)
Else
wksNew.Cells (iTargetRow, L) _
= Temp (K)
End If
Nästa L
Nästa K
Nästa J
End Med
End Sub

Observera att för att köra makrot, måste du ange, med hjälp av iColumn variabeln, den kolumn som innehåller celler som skall delas isär. Som skrivet här, delar upp makrot isär info i den fjärde kolumnen. Dessutom är de delade-isär versioner av cellerna som lagras i ett nytt kalkylblad, så att den ursprungliga kalkylbladet inte påverkas alls.

Makrot bygger på användningen av Split funktionen att riva isär flera linjer celler. Denna funktion är endast tillgänglig från och med Excel 2000, och är inte tillgänglig i Excel för Mac alls. Dessutom kanske du vill bara köra makrot på en viss urval av celler. För att övervinna alla dessa potentiella problem, kommer du vill överväga följande makro, istället:

Sub CellSplitter2 ()
Dim iSplitCol As Integer
Dim iEnd As Integer
Dim Stemp As String
Dim iCount As Integer
Dim I As Integer
Dim wksNew Som arbetsblad
Dim wksSource Som arbetsblad
Dim lRow As Long
Dim lRowNew As Long
Dim lRows som Long
Dim lRowOffset As Long
Dim iTargetRows As Integer
Dim iCol As Integer
Dim iCols As Integer
Dim iColOffset As Integer
Dim AWF Som WorksheetFunction

On Error GoTo ErrRoutine
Application.ScreenUpdating = False

"Ställ Kolumn att dela
iSplitCol = 4

iCols = Selection.Columns.Count
lRows = Selection.Rows.Count

iColOffset = Selection.Column - 1
lRowOffset = Selection.Row - 1
lRowNew = lRowOffset

Ställ wksSource = ActiveSheet
Ställ wksNew = Worksheets.Add

Ställ AWF = Application.WorksheetFunction
Med wksSource
För lRow = (lRowOffset + 1) Till (lRowOffset + lRows)
Stemp = .Cells (lRow, iSplitCol)
Om Höger (Stemp, 1) <> vbLf Sedan
Stemp = Stemp & vbLf
End If
iCount = (Len (Stemp) - _
Len (AWF.Substitute (Stemp, vbLf, "")))

För iTargetRows = 1 Till iCount
lRowNew = lRowNew + 1
För i = (iColOffset + 1) Till (iColOffset + iCols)
Om jag <> iSplitCol Sedan
wksNew.Cells (lRowNew, i) _
= .Cells (LRow, i)
Else
iEnd = InStr (Stemp, vbLf)
wksNew.Cells (lRowNew, i) _
= Vänster (Stemp, iEnd - 1)
Stemp = Mid (Stemp, iEnd + 1)
End If
Nästa jag
Nästa iTargetRows
Nästa lRow
End Med

ExitRoutine:
Ställ wksSource = Ingenting
Ställ wksNew = Ingenting
Ställ AWF = Nothing
Application.ScreenUpdating = True
Exit Sub

ErrRoutine:
MsgBox Err.Description, vbExclamation
Resume ExitRoutine
End Sub

Makrot fortfarande förlitar sig på användningen av en variabel för att ange kolumnen att delas isär. I detta fall är variabeln iSplitCol, och den är inställd på kolumn 4. makrot fungerar bara på cellerna utvalda när det är första körningen, och de delade-isär celler överförs till ett nytt kalkylblad. Adressen till övre vänstra cellen i den nya kalkylbladet är samma som den övre vänstra cellen väljs när makrot körs.

ExcelTips är din källa för kostnadseffektiv Microsoft Excel utbildning. Detta tips (3263) gäller för Microsoft Excel 97, 2000, 2002, och 2003. Du kan hitta en version av detta tips för menyfliksområdet i Excel (Excel 2007 och senare) här: Dela upp information i rader.