SOLVED

Formula to split cell values to multiple cells based on condition

Copper Contributor

Is it possible to split a cell in to multiple cells based on the condition?

 

If a cell contains a value like (A01626*A01666*6*A01656*A01696)/(A01646*100*A01676*6*1.1) in Cell A1, then is it possible to split to multiple cells only the value starts with A0? Like shown in below picture?

 

2018-12-05_10-41-58.png

Regards

3 Replies

Are the strings you want to pull out always going to be 6 characters? If not what the characters do we want to exclude? Just "(" , ")" , "/" and "*" ?

 

If it is always 6 characters then here is VBA solution based on selecting the range containing the cells to be split if that works for you. If it is not always 6 characters let me know what the seperator characters need to be and I will rejig this. 

 

Sub SplitString()

Dim Cell As Range
Dim i As Integer
Dim Position As Integer
Dim Search As String
Dim Start As Integer

Search = "A0"

For Each Cell In Selection

i = Cell.Column + 1

Start = 1

Do

Position = InStr(Start, Cell.Text, Search, vbTextCompare)

If Position > 0 Then
Start = Position + 1
Cells((Cell.Row), i).Value = Mid(Cell.Text, Position, 6)
i = i + 1
End If

Loop While Position > 0

Next Cell

End Sub

 

Thank you  and it is working for the first cell. How the code can automatically check the whole column and split?

 

Much appreciated in advance.

 

Regards,

Senthil

best response confirmed by Senthilraja_Ramasamy (Copper Contributor)
Solution

You should just be able to select the whole range you want to split and then run it. I tried it with multiple cells selected when I wrote it. 

 

Or just use this, change Set SplitRange = Range("A1:A1000") as appropriate to the range you want to split the strings from. 

 

Sub SplitString()

Dim Cell As Range
Dim i As Integer
Dim Position As Integer
Dim Search As String
Dim Start As Integer
Dim SplitRange As Range

Search = "A0"

Set SplitRange = Range("A1:A1000")

For Each Cell In SplitRange

i = Cell.Column + 1

Start = 1

Do

Position = InStr(Start, Cell.Text, Search, vbTextCompare)

If Position > 0 Then
Start = Position + 1
Cells((Cell.Row), i).Value = Mid(Cell.Text, Position, 6)
i = i + 1
End If

Loop While Position > 0

Next Cell

End Sub

 

 

 

 

1 best response

Accepted Solutions
best response confirmed by Senthilraja_Ramasamy (Copper Contributor)
Solution

You should just be able to select the whole range you want to split and then run it. I tried it with multiple cells selected when I wrote it. 

 

Or just use this, change Set SplitRange = Range("A1:A1000") as appropriate to the range you want to split the strings from. 

 

Sub SplitString()

Dim Cell As Range
Dim i As Integer
Dim Position As Integer
Dim Search As String
Dim Start As Integer
Dim SplitRange As Range

Search = "A0"

Set SplitRange = Range("A1:A1000")

For Each Cell In SplitRange

i = Cell.Column + 1

Start = 1

Do

Position = InStr(Start, Cell.Text, Search, vbTextCompare)

If Position > 0 Then
Start = Position + 1
Cells((Cell.Row), i).Value = Mid(Cell.Text, Position, 6)
i = i + 1
End If

Loop While Position > 0

Next Cell

End Sub

 

 

 

 

View solution in original post