Clash Royale CLAN TAG#URR8PPP

Spliting numbers into different colums using vba

This is my excel sheet:

I need to split numbers into beside columns without pull stops, I'm new to VBA can u please suggest me how to write code.

enter image description here

I want to get data into like this

Thank you

split()

.

trim()

3 Answers
3

You need something like this, I think.

Sub SplitStuff()
'Update 20140318
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In InputRng
xValue = Rng.Value
xRow = Rng.Row
For i = 1 To VBA.Len(xValue)
OutRng.Cells(xRow, i).Value = VBA.Mid(xValue, i, 1)
Next
Next
Application.ScreenUpdating = True
End Sub


Or use this function; fill down and fill right.

=MID($A1, COLUMNS($A$1:A$1), 1)


Using Macro Recording with text to columns I got the below code. You can customize as you wish. Hope it will useful to you.

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=".", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True


Dim Rng As Range, B As Range
Dim x As Integer, H As Integer

Set Rng = ThisWorkbook.Sheets(2).Range("B1:B220")

x = 1
For Each c In Rng
For y = 1 To Len(c.Value)
Rng(c.Row, x) = Mid(c.Value, y, 1)
x = x + 1
Next y
x = 2
Next c


This works for me..
Thank you!!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.