Hi Guys,
Can you help me parse the strings on the attached sample file.
Thanks in advance.
Hi Guys,
Can you help me parse the strings on the attached sample file.
Thanks in advance.
Last edited by ivhee00; 02-22-2016 at 06:02 PM.
Try
Sub test() Dim r As Range With CreateObject("VBScript.RegExp") .Pattern = "^(\d+)\W+([A-Za-z]+ \d+)(\W+(.+))?$" For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp)) If .test(r.Value) Then r(, 2).Resize(, 3).Value = Split(.Replace(r.Value, "$1^$2^$4"), "^") End If Next End With End Sub
Maybe something like this, to handle multiple delimiters.Sub Delimit_Data() Dim i As Integer, j As Integer, ws As Worksheet, sText As String, arText() As String Set ws = ThisWorkbook.Worksheets("Sheet2") For i = 2 To ws.Range("A" & Rows.Count).End(xlUp).Row sText = ws.Range("A" & i) sText = Replace(sText, "/", " ") sText = Replace(sText, "\", " ") sText = Replace(sText, "(", " ") sText = Replace(sText, ")", " ") sText = Replace(sText, "*", " ") sText = Replace(sText, "&", " ") sText = Replace(sText, "-", " ") ' add more delimiters as needed sText = Application.Trim(sText) arText = Split(sText) For j = LBound(arText) To UBound(arText) ws.Cells(i, j + 2) = arText(j) Next j Next i Set ws = Nothing End Sub
Hi Jewelsharma,
Thanks so much for the help.. I used your code for the meantime and just replace " " to "/".
Hi Jindon,
Thanks you so much for the help. I want to use your code but my data doesn't always start with a number and it didn't work if it start with a word. Can you help me on this..
Perhaps
Sub test() Dim r As Range With CreateObject("VBScript.RegExp") .Pattern = "^((\d+)\W+)?([A-Za-z]+ \d+)(\W+(.+))?$" For Each r In Range("a2", Range("a" & Rows.Count).End(xlUp)) If .test(r.Value) Then r(, 2).Resize(, 3).Value = Split(.Replace(r.Value, "$2^$3^$5"), "^") End If Next End With End Sub
Last edited by jindon; 02-12-2016 at 02:53 AM. Reason: $1 replaced with $2
Thanks guys! You are amazing.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks