1

I have a trouble trying to figure out how to solve this in VBA. I need to put the correct information in its respective cell, for example:

Example: enter image description here

In the cell range Name should be only the name Amanda for first row...

I've been using a macro but it doesn't work since it only works for 1 delimiter. Any idea how to solve this?

Naresh
  • 2,984
  • 2
  • 9
  • 15
Andy V
  • 25
  • 3
  • 1
    Multiple steps - first, use the comma to split, then split each result item by the equal sign or dash... just keep looping the results. – braX Aug 18 '21 at 02:29
  • 1
    [Regex](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) might be another option. – BigBen Aug 18 '21 at 02:29
  • If we can do it by formula then is it okay? What is your excel version? – Harun24hr Aug 18 '21 at 02:36

2 Answers2

1

Try this code

Sub SplitValuesToNextColumns()
Dim rng As Range, cl As Range, str As String
Dim regex As Object, mc As Object

Set regex = CreateObject("VBScript.regexp")
regex.ignorecase = False
regex.Global = True

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select column range" & vbLf & _
        "Macro result will spill in the next 4 columns of the selected range", _
        Title:="Select Range", Default:="B3", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cl In rng
    str = Replace(Replace(Replace(Replace(cl.Value, _
        "1-Name=", "["), _
        ", 2-Last Name=", "]["), _
        ", 3-Address=", "]["), _
        ", 4-Status=", "][") & "]"

    regex.Pattern = "\[[^]]+\]"
    Set mc = regex.Execute(str)
    For i = 0 To mc.Count - 1
        Cells(cl.Row, cl.Offset(, i + 1).Column) = _
                    Mid(mc(i), 2, Len(mc(i)) - 2)
    Next i
Next cl

End Sub

EDIT - No need for regex

As suggested by @RaymondWu in comments below.

Sub SplitValuesToNextColumns()
Dim rng As Range, cl As Range, str As String, arr

On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select column range" & vbLf & _
        "Macro result will spill in the next 4 columns of the selected range", _
        Title:="Select Range", Default:="B3", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
For Each cl In rng
    str = Replace(Replace(Replace(Replace(cl.Value, _
        "1-Name=", ""), _
        ", 2-Last Name=", "|"), _
        ", 3-Address=", "|"), _
        ", 4-Status=", "|")
    arr = Split(str, "|")
        cl.Offset(, 1).Resize(1, UBound(arr) + 1) = arr
Next cl

End Sub

enter image description here

Naresh
  • 2,984
  • 2
  • 9
  • 15
  • Edited the code. Earlier code could not work well if there are commas between the values say address. This code will work well. – Naresh Aug 18 '21 at 04:50
  • 1
    Amazing! It really worked :) I've never used regex tho. It's time to learn a new thing! Thanks buddy. – Andy V Aug 18 '21 at 04:50
  • 1
    Would it be better if you replace with a delimiter (except for the first replace which would be vbNullstring instead) then use Split? The resulting array can then be written into the 4 cells directly. – Raymond Wu Aug 18 '21 at 05:54
  • 1
    @RaymondWu Yes. .. Brilliant.. Hahaha, I missed that. Editing the answer. – Naresh Aug 18 '21 at 06:31
0

No formulas, I need to complete this by only using VBA. What I just did was this: VBA.Example

But as you can see, it doesn't work properly cause i need to add another delimiter or it probably should be done in another way.

Andy V
  • 25
  • 3
  • Hi Andy. It would have been better to post your code tentative in your Question, and we usually prefers code to be posted as text rather than screen capture. Remember you can Edit your question. – Vincent G Aug 18 '21 at 06:30