文本到多列的列-Excel VBA [英] Text to columns for multiple columns - Excel VBA
本文介绍了文本到多列的列-Excel VBA的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有许多列的连接数据,我想按空格分割.
I have many columns of concatenated data that I would like to split by spaces.
因此,此:
对此:
此VBA代码非常接近
Sub TextToColumns()
'Deines Last Row
Dim LastRow As Long
LastRow = 1048576 'the last row possible in excel
'optional alternative **LastRow** Code
'Counts number of rows (counts from last row of Column A):
'Dim LastRow As Long
'LastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Counts number of Columns (my headers start in row 1)
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
'Loops Text to columns
Dim StartingRow, StartingColumn As Long
StartingRow = 1
For StartingColumn = 1 To LastColumn
Range(Cells(StartingRow, StartingColumn), Cells(LastRow, StartingColumn)).Select
Selection.TextToColumns , DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Next
End Sub
但是我只想在选定的单元格上使用它,它会覆盖数据以提供此信息:
but I would like to use it only on the selected cells, and it overwrites the data to give this:
如何避免覆盖数据,而仅在选定的单元格上运行宏?非常感谢.
How can I avoid overwriting the data, and only run the macro on selected cells? Thank you very much.
推荐答案
尝试此代码.基本上,它的作用是循环遍历选定的行,并将该列的每次销售中的所有文本合并为一个字符串,然后将其拆分为该列中的每个单元格,并以空格作为定界符.
Try this code. Basicly what it does is that it loops throug the selected rows and merge all the text in each sell of the column into a string, then it splits it up into each cell in the column with space as a delimiter.
在运行宏之前,请记住要选择一些行.
Sub TextToColumns()
'Counts number of Columns (my headers start in row 1)
Dim LastColumn As Long
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
'Full strig
Dim FullString As Variant
'Split string
Dim SplitString As Variant
'Loops Text to columns
Dim rng As Range
Dim lRowSelected As Long
For Each rng In Selection.Rows
RowsSelected = rng.Row
'Making one string from all the cells in the row
For StartingColumn = 1 To LastColumn
If StartingColumn = 1 Then
FullString = Cells(RowsSelected, StartingColumn).Value
Else
FullString = FullString & " " & Cells(RowsSelected, StartingColumn).Value
End If
Next StartingColumn
'Splits the string up into each cell with space as a delimiter
SplitString = Split(FullString, " ")
For i = 0 To UBound(SplitString)
Cells(RowsSelected, i + 1).Value = SplitString(i)
Next i
Next rng
End Sub
这篇关于文本到多列的列-Excel VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文