I have data in one column and I want to split it into multiple columns. For example I have data like
123*4546.765,4653:342.324
I want to split this as 123 in a column, 4546 in a column, 765 in a column, 4653 in a column and so on ...
-
How are you thinking it could be done or how to do it. I guess you'd have to use Range.TextToColumns method. Tab, Comma, Semi-colon and one other character per pass. A non-trivial task in VBA so you could start by recording some macros then refining them in the IDE. When you have some substantial code that isn't quite working then post a question.Mark Fitzgerald– Mark Fitzgerald2017-02-17 09:10:31 +00:00Commented Feb 17, 2017 at 9:10
Add a comment
|
1 Answer
Try the code below (using the RegEx).
The RegEx pattern is looking for any consecutive 1 to 10 digits .Pattern = "[0-9]{1,10}".
Then, if at least 1 Match found, loop inside all Matches and output them (from Cell "A1" and move one column to the right).
Code
Option Explicit
Sub ExtractNumbers()
Dim Col As Long
Dim Reg1 As Object
Dim RegMatches As Variant
Dim Match As Variant
Set Reg1 = CreateObject("VBScript.RegExp")
With Reg1
.Global = True
.IgnoreCase = True
.Pattern = "[0-9]{1,10}" ' Match any set of 9 digits
End With
Set RegMatches = Reg1.Execute("123*4546.765,4653:342.324") '<-- you can use a `Range` value instead of hard-code
If RegMatches.Count >= 1 Then '<-- check that at least 1 match made with the RegEx
Col = 1
For Each Match In RegMatches
MsgBox Match ' <-- output in msgbox
Cells(1, Col).Value = Match '<-- output the numbers from Cell A1 and move one column to the right
Col = Col + 1
Next Match
End If
End Sub
