CODEWITHSUNDEEP
excelvbasplitcells
NDubonix
NDubonix

Reputation: 103

Split Cell by Numbers Within Cell

I have some fields that need to be split up into different cells. They are in the following format:

Numbers on Mission 21 0 21

Numbers on Mission 5 1 6

The desired output would be 4 separate cells. The first would contain the words in the string "Numbers on Mission" and the subsequent cells would have each number, which is determined by a space. So for the first example the numbers to extract would be 21, 0, 21. Each would be in its own cell next to the string value. And for the second: 5, 1, 6.

I tried using a split function but wasn't sure how to target the numbers specifically, and to identify the numbers based on the spaces separating them.

Upvotes: 0

Views: 164

Answers (3)

Alexander Bell
Alexander Bell

Reputation: 7918

Pertinent to your first case (Numbers on Mission), the simple solution could be as shown below:

Sub SplitCells()
Const RowHeader As String = "Numbers on Mission"
Dim ArrNum As Variant
ArrNum = Split(Replace(Range("A1"), RowHeader, ""), " ")
For i = 1 To UBound(ArrNum)
    Cells(1, i + 2) = ArrNum(i)
Next
Cells(1, 2) = RowHeader
End Sub

The same logic is applicable to your second case. Hope this may help.

Upvotes: 1

NDubonix
NDubonix

Reputation: 103

I was able to properly split the values using the following:

 If i.Value Like "*on Mission*" Then

x = Split(i, " ")

    For y = 0 To UBound(x)

    i.Offset(0, y + 1).Value = x(y)

    Next y

End If

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23283

Unless I'm overlooking something, you may not need VBA at all. Have you tried the "Text to Columns" option? If you select the cell(s) with the information you would like to split up, and go to Data -> Text to Columns. There, you can choose "delimited" and choose a space as a delimiter, which will split your data into multiple cells, split by where the space is.

edit: Just realized that will also split up your string. In that case, when you are in 3rd part of the Text to Columns, choose a destaination cell that isn't the cell with your data. (I.E. if your data is in A1, choose B1 as destination, and it'll put the split info there. Then just combine the text columns with something like =B1&" "&C1&" "&D1)

Upvotes: 0

Related Questions