• 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.

Split Email Ids next cells

Abhijeet

Active Member
Hi

I have 4 to 5 email Ids in one cell i want to split all email Ids next to that cell i know text to column is do this but can u pls tell me Vba solution for this

Thanks in advance
 

Hi !

Even with VBA I use TextToColumns method !

Beginner level, juste read its VBA inner help …
Or create the code by activating Macro Recorder !
 
Record a macro of you doing a Text to columns. You'll see that the method is called, no surprise, TextToColumns. You can then use the VB help if you need to change any arguments.
 
Hi Both

I have already do this but i want to learn Split Function of VBA.In my data before & after each id space i have Trim macro also i use all these but if VBA Split function give perfect Only Email Ids then why we do lengthy process this question come in my mind so i ask this question on forum
Code:
Sub Macro1()
'
' Macro1 Macro
'

'
    Selection.TextToColumns Destination:=Range("B2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=";", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub
 


When data are well organized and with a purpose to separate data
to columns, TextToColumns method is the fastest !
 

When data are well organized and with a purpose to separate data
to columns, TextToColumns method is the fastest !
But i have Spaces in that data so i want to remove all those then that email ID send the email can u please help me to do all in VBA
 
Hi Marc L

I know this method but if in VBA Split function then pls tell me VBA solution excel how to do this i know pls understand me i am new in VBA thats why i am ask this question other wise excel this way i know how to do this
 

You can't be new in VBA with almost 700 posts just in this forum ‼

If you use Split function within a variable,
elements splitted are in this array variable.
Check its result in Local variables window : it's crystal clear !

See samples in this forum threads …
 
I am doing this solution please tell me is this correct or ?
Code:
For Each Rng In ActiveSheet.Range("a1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
arr = Split(Rng, ";")
Rng.Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
Next
 
TextToColumns is meant to split text over a range of cells.
Split is meant to split text into multiple strings within VB.

If you wanted to learn about Split, state that in your OP. Don't say you want to split cells into range and then refuse to accept the best answer. You're trying to do the equivalent of making 3 left turns instead of 1 right turn. Or a better metaphor would be if you asked "How do I turn on a car?" and we gave the answer "Turn the key", and you then said "Well I want to use a piece of wire". You might get the same result eventually, but you made it 100x more complicated. :p

As Marc said, you aren't new to VB. We've been helping you in this forum for quite some time. Take some time to read through some of the previously posted code (in your own threads and in others), learn how it works, and read the VB help.
 
TextToColumns is meant to split text over a range of cells.
Split is meant to split text into multiple strings within VB.

If you wanted to learn about Split, state that in your OP. Don't say you want to split cells into range and then refuse to accept the best answer. You're trying to do the equivalent of making 3 left turns instead of 1 right turn. Or a better metaphor would be if you asked "How do I turn on a car?" and we gave the answer "Turn the key", and you then said "Well I want to use a piece of wire". You might get the same result eventually, but you made it 100x more complicated. :p

As Marc said, you aren't new to VB. We've been helping you in this forum for quite some time. Take some time to read through some of the previously posted code (in your own threads and in others), learn how it works, and read the VB help.
Luke M but what i tried is this correct please tell me
ForEach Rng In ActiveSheet.Range("a1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
arr = Split(Rng, ";")
Rng.Offset(0, 1).Resize(1, UBound(arr) + 1).Value = arr
Next
 
@ Abhijeet

This is just what u are trying but failed to accomplish even after luke & marc guidance. This shows how you handle what we are suggested here!

You must have to be responsible to do the revision until or unless u get the key why u failed yet.

Code:
Sub test()
Dim Rng As Range, arr As Variant
    For Each Rng In ActiveSheet.Range("a1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        arr = Split(Rng, ";")
        Rng.Offset(0, 1).Resize(, UBound(arr) + 1).Value = arr
    Next
End Sub
 
Back
Top