• 461K Members
• 12.4K Online
• 558K Conversations
SOLVED

New Contributor

# Formula to split cell values to multiple cells based on condition

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?

Regards

3 Replies

# Re: Formula to split cell values to multiple cells based on condition

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 RangeDim i As IntegerDim Position As IntegerDim Search As StringDim Start As IntegerSearch = "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 > 0Next CellEnd Sub`

# Re: Formula to split cell values to multiple cells based on condition

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

Regards,

Senthil

Solution

# Re: Formula to split cell values to multiple cells based on condition

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 RangeDim i As IntegerDim Position As IntegerDim Search As StringDim Start As IntegerDim SplitRange As RangeSearch = "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 > 0Next CellEnd Sub`

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies