• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

segregation into different column by vba as well as formula [Solved]

Status
Not open for further replies.
P

paradise

Member
Hi,

I have a raw data in B2:B4.This data i want to separate into different column as mentioned in columns D:I.

I am expecting the solution in formula as well as in VBA.Pls note that this is a sample data only as there numerous.

Thanx in advance.
 

Attachments

  • Segregation of data.xlsx
    8.2 KB · Views: 1
For VBA Approach try this..

Code: 
Sub mysplit()
Dim rng As Range, r As String, a As Integer, mystr As String, l As Integer

For Each rng In Range("B2:B4")
    r = UCase(rng) & "-"
    l = 1
    For i = 1 To Len(r)
        Select Case Asc(Mid(r, i, 1))
            Case 65 To 90, 48 To 57, 32
            Case Else
                mystr = mystr & Mid(r, l, i - l) & Chr(10)
                l = i + 1
        End Select
    Next
rng.Offset(, 2).Resize(, UBound(Split(mystr, Chr(10)))) = Split(mystr, Chr(10))
mystr = ""
Next

End Sub
 
There is one more word 'M.POLY-3' When I am using your formula it is separating into3 as m,poly and 3 however it should be 2 as M.POLY and 3.
Kindly check by typing 'M.POLY-3' in B5
 
It now works can u explain me those digits Case 65 To 90, 48 To 57, 32, 46
Here my result was from D2 column onwards.How can get those results as per user defined in the same row but in different column which is currently defined in D.

Kindly let me know.
 
I have commented some parts!!

Check this & let me know if further clarity is req.


Code: 
Sub mysplit()
Dim rng As Range, r As String, a As Integer, mystr As String, l As Integer

'Range("B2:B4") is the range where data is
For Each rng In Range("B2:B4")
'ucase convert it to upper
    r = UCase(rng) & "-"
    l = 1
    For i = 1 To Len(r)
    'check asc key of each char
        Select Case Asc(Mid(r, i, 1))
    'ignore if the char is A-Z,0-9,.,space
            Case 65 To 90, 48 To 57, 32
            'ignore
            Case Else
                mystr = mystr & Mid(r, l, i - l) & Chr(10)
                l = i + 1
        End Select
    Next
'put the splitted value in D

'To put the data in another col just change the offset value rng.Offset(, X)
'where X = where to put col - data col
'like as here have to put in D that is 2 col far from col B
'or simply columns(D)-Columns(B)
'X=4-2 = 2

rng.Offset(, 2).Resize(, UBound(Split(mystr, Chr(10)))) = Split(mystr, Chr(10))
mystr = ""
Next

End Sub
 
Thanx bro.Your stepwise method of explaining formula is really awesome.I really like it.

Lastly, here in above I have shown some specific range.In reality every now and then I can't change the range becoz day by day it goes on increasing.Here how can I define range in this case ?

Your help in this is highly appreciated.
 

Hi !

This is my easy fast way whatever the source range :​
Code: 
Sub Demo()
With Sheet1
    With .Cells(4).CurrentRegion.Rows
      If .Count > 1 Then .Item("2:" & .Count).ClearContents
    End With
   
    Application.ScreenUpdating = False
    VA = .Cells(2).CurrentRegion.Value
    ReDim S$(2 To UBound(VA), 0)
   
    For R& = 2 To UBound(VA)
        For Each W In Split(VA(R, 1), "/")
            For Each V In Split(W, "-"):  S(R, 0) = S(R, 0) & V & vbTab:  Next
        Next
    Next
   
    With .[D2].Resize(R - 2):  .Value = S:  .TextToColumns:  End With
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Thanx for the code.Can u explain your code in the manner as it was explained in above code i.e in post # 5 and taking into account the condition as mentioned in post#6.
 

Best is to read post and try code before writing anything !

For each statement you need info, put text cursor on it,

press
f1.gif
key and read !

​
 
Yeah.That is already solved for formulas and here by VBA.I am trying to know maximum possible ways by different forums to enhance my skills of learning.

If that hurts u,I am extremely sorry for that.In that post u know that it has already been marked solved.
 
Status
Not open for further replies.
Back
Top