Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Criscristina92 Profile
 | Femeie
25 ani
Bucuresti
cauta Barbat
29 - 80 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
CHOOSE CHARACTER TO SPLIT BY VIA INPUT BOX ---- SPLIT IS DONE FOR SELECTION, IN THE ADJACENT COLUMNS
Sub SplitMe3()
Application.ScreenUpdating = False
With ActiveSheet
Dim myRange As Range
Dim varInput As String
Set myRange = Selection
varInput = InputBox("Select desired delimiter:")
Selection.TextToColumns destination:=myRange(rw + 1, col + 1), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, other:=True, OtherChar:=varInput
End With
Application.ScreenUpdating = True
End Sub |
RESET SPLIT IN THE SELECTED CELL ---- PICK AN EMPTY CELL BECAUSE CONTENTS WILL BE DELETED
Sub SplitMe4()
Application.ScreenUpdating = False
With ActiveSheet
Dim myRange As Range
Set myRange = Selection
myRange = "A" 'Required; you must have something to parse
Selection.TextToColumns destination:=myRange, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, other:=False
myRange = ""
End With
Application.ScreenUpdating = True
End Sub |
Modificat de TRaP (acum 6 ani)
|
|
| pus acum 6 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
More possibilities (UNTESTED)
There are multiple codes you can use.
The simplest:
Sub SplitMe()
Range("A:A").Select
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="|"
End Sub |
Based on an array:
Sub SplitMe2()
Dim N As Long, wf As WorksheetFunction
Set wf = Application.WorksheetFunction
N = Cells(Rows.Count, "A").End(xlUp).Row
Dim i As Long, j As Long, k As Long
For i = 1 To N
ary = Split(wf.Trim(Cells(i, "A").Text), " ")
k = 2
For j = LBound(ary) To UBound(ary)
Cells(i, k).Value = ary(j)
k = k + 1
Next j
Next i
End Sub |
Excel remembers your choice and in the future it splits your data automatically. If you want to stop this, you will have to run some code to reset it. You can add the following code to one of the above, to always reset the text to columns after you make your split.
Sub Row_A_SPLIT_STOP()
Cells(1) = "A" 'Required; you must have something to parse
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=False
Cells(1) = "" 'Reset cell contents to nothing
End Sub |
|
|
| pus acum 6 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
A version of the simple code above, but to split only used cells from column A to columns B and C starting from row 2:
Sub ExtractHL()
Dim LR As Long
LR = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' Range("A2:A" & LR).Select
Range("A2:A" & LR).TextToColumns Destination:=Range("B2:C2" & LR), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:=", "
End Sub |
_______________________________________
|
|
| pus acum 4 ani |
|