How to split Column Value under Particular column Name

Question

I have the Main Value as per below table. Now, I need to keep the each value under particular column name .All the values are space separated. Let say, the Main value is:2 3 6 7.

Now, 2 should goes under the column name 2. 3 Should goes under the column name 3 and same as 6 should goes under the column name 6 and 7 should goes under the column name 7. In excel there have a option name "text to column". Using this function the value can split but it's not goes to it's particular column name.

Please help , how shall I do that. Thanks in advance. [I am using "Microsoft Office Professional Plus 2019"]

Answer 1

Here is what you could do, if you are not using MS365:

---

• Formula used in cell B3

=LET(x,FILTERXML("<m><b>"&SUBSTITUTE($A3," ","</b><b>")&"</b></m>","//b"),
IFERROR(INDEX(x,MATCH(1,N(x=B$1),0)),""))

---

And Fill Right & Fill Down!

---

Using MS365 Functions --> TEXTSPLIT()

---

• Formula used in cell B3

=LET(x,TEXTSPLIT($A3,," "),
IFERROR(INDEX(x,BYCOL($B$1:$K$1,LAMBDA(m,XMATCH(1,N(x+0=m))))),""))

---

And Fill Down, no need to fill right as it spills.

---

Since you are using Microsoft Office Professional Plus 2019 you need to hit the CTRL+SHIFT+ENTER while exiting the edit mode.

---

• Formula used in cell B3

=IFERROR(INDEX(FILTERXML("<m><b>"&SUBSTITUTE($A3," ","</b><b>")&"</b></m>","//b"),
MATCH(1,N(FILTERXML("<m><b>"&SUBSTITUTE($A3," ","</b><b>")&"</b></m>","//b")=B$1),0)),"")

---

You can also define a name for the FILTERXML() formula

---

• Formula used in cell B3

=IFERROR(INDEX(MAIN_VALUE,MATCH(1,N(MAIN_VALUE=B$1),0)),"")

---

Where MAIN_VALUE

=FILTERXML("<m><b>"&SUBSTITUTE($A9," ","</b><b>")&"</b></m>","//b")

---

And you need to hit CTRL+SHIFT+ENTER after entering the formula and exiting the edit mode.

---

EDIT: Formula found to be working for OP

---

• Formula used in cell B3

=IFERROR(LOOKUP(2,1/SEARCH(" "&B$1&" "," "&$A3&" "),B$1),"")

---

And Fill Down & Fill Right!

---

More simple & succinct alternative as proposed by JvdV Sir

---

• Formula used in cell B3

=IF(ISNUMBER(FIND(" "&B$1," "&$A3&" ")),B$1,"")

---

And Fill Down & Fill Right!

---

Answer 2

Split & Group Data (VBA)

Sub SplitData()
    
    ' Define constants.
    Const DELIMITER As String = " "
    
    ' Reference the worksheet.
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    ' Reference the range.
    Dim rg As Range: Set rg = ws.UsedRange
    
    ' Write the row labels to an array.
    Dim rCount As Long: rCount = rg.Rows.Count - 1
    Dim rrg As Range: Set rrg = rg.Columns(1).Resize(rCount).Offset(1)
    Dim rData(): rData = rrg.Value
    
    ' Write the column labels to an array.
    Dim cCount As Long: cCount = rg.Columns.Count - 1
    Dim crg As Range: Set crg = rg.Rows(1).Resize(, cCount).Offset(, 1)
    Dim cData(): cData = crg.Value
    
    ' Convert the column labels to strings.
    Dim c As Long
    For c = 1 To cCount
        cData(1, c) = CStr(cData(1, c))
    Next c
    
    ' Reference the values range.
    Dim vrg As Range: Set vrg = rrg.Offset(, 1).Resize(, cCount)
    ' Define the values array.
    Dim vData(): ReDim vData(1 To rCount, 1 To cCount)
    
    ' Declare For...Next loop variables.
    Dim cIndexes, cIndex, SubStrings() As String, r As Long, rStr As String
    
    ' Loop through the row labels.
    For r = 1 To rCount
        rStr = CStr(rData(r, 1))
        If Len(rStr) > 0 Then
            ' Split the current row label into an array.
            SubStrings = Split(rStr, DELIMITER)
            ' Return the matching column indexes in an array.
            cIndexes = Application.Match(SubStrings, cData, 0)
            ' Write the matching values to the values array.
            For Each cIndex In cIndexes
                'Debug.Print cIndex
                If IsNumeric(cIndex) Then
                    vData(r, cIndex) = cData(1, cIndex)
                End If
            Next cIndex
        End If
    Next r

    ' Write the values array to the values range.
    vrg.Value = vData

End Sub

EDIT

• Added rStr, i.e. If Len(rStr) > 0 Then to account for type-mismatch error occurring when the row label (the value in column A) was blank.