Split a text using VBA
 

Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

Sub splitThem()
Dim s As String
s = "immm_JASON_PINTO_DAVID_WEST_1000011"
Range("A1:E1") = Split(s, "_")
End Sub

--
Gary''s Student - gsnu200908

Split a text using VBA
 

Try the below

Dim strData As String, arrData As Variant
strData = "immm_JASON_PINTO_DAVID_WEST_1000011"
arrData = Split(strData, "_")
Range("A2").Resize(1, UBound(arrData) + 1) = arrData

If this post helps click Yes
---------------
Jacob Skaria


"ub" wrote:

Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

Hi
Record a macro while you do this manually using Text to Columns
(within Data menu). Should give you a good start.
regards
Paul

On Oct 21, 1:39*pm, ub wrote:
Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

Hi

With a text in a variable it can be done like this:

Sub aaa()
myarr = Split("immm_JASON_PINTO_DAVID_WEST_1000011", "_")
c = UBound(myarr) + 1
Range("A1").Resize(1, c) = myarr
End Sub

If your many cells with text to split, you should look at TextToColumns in
help.

Regards,
Per

"ub" skrev i meddelelsen
...
Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

Hi Thanks for your help. I works.
Is there a way if I only want to extract "JASON PINTO" & "DAVID WEST" from
this string

"Jacob Skaria" wrote:

Try the below

Dim strData As String, arrData As Variant
strData = "immm_JASON_PINTO_DAVID_WEST_1000011"
arrData = Split(strData, "_")
Range("A2").Resize(1, UBound(arrData) + 1) = arrData

If this post helps click Yes
---------------
Jacob Skaria


"ub" wrote:

Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

You're statement is not entirely clear... do you mean you want the names
Jason Pinto David and West each in their own column (like your original post
would indicate) or that "JASON PINTO" should go in one column and "DAVID
WEST" should go in another column? Whichever way you answer that question,
you will need to tell us about the structure of the text you will be
processing... is there **always** one piece of text before the first
underbar and one piece of text after the last underbar that will be
discarded and will there **always** be four names to be grouped into two
full names (as your example shows)? In other words, you will never have
middle names, middle initials or first or last names made up of two
individual names to process. I ask this last part because I had two friends
at work (before I retired) with these names... Mary Ann Smith (Mary Ann was
her first name) and Anthony Della Rossa (Della Rossa was his last name).

--
Rick (MVP - Excel)


"ub" wrote in message
...
Hi Thanks for your help. I works.
Is there a way if I only want to extract "JASON PINTO" & "DAVID WEST" from
this string

"Jacob Skaria" wrote:

Try the below

Dim strData As String, arrData As Variant
strData = "immm_JASON_PINTO_DAVID_WEST_1000011"
arrData = Split(strData, "_")
Range("A2").Resize(1, UBound(arrData) + 1) = arrData

If this post helps click Yes
---------------
Jacob Skaria


"ub" wrote:

Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

Thanks Paul

I tried using textiocolumns in vba for text "JASON AND PINTO AND DAVID AND
WEST". this text is in column A. What I am trying is to put each string in
different column staring from Column B
I wrote:
For i = 2 To 100
Range("a" & i).TextToColumns Destination:=Range("b" & i), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
other:=True, _
otherchar:="and"

But what I am getting is that column B has same text as column A. please
advise how to correct my code
" wrote:

Hi
Record a macro while you do this manually using Text to Columns
(within Data menu). Should give you a good start.
regards
Paul

On Oct 21, 1:39 pm, ub wrote:
Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

.

Split a text using VBA
 

The reason you got the same text back is because your delimiter is made up
of more than one character... the "char" in the "otherchar" argument is
singular (character), not plural... it worked for Paul because your original
text used a single underbar character as the "otherchar". Parsing text
requires a precise knowledge of the structure of the text that is being
parsed and the code used to parse the text is designed directly for handling
that structure... unfortunately, you haven't provided that structure for us.
Your first post used underbar characters as delimiters and this example used
the word AND surrounded by spaces; also, your first post appeared to have
words in the text that you didn't want parsed whereas you current example
doesn't. If you can define the **structure** of the text you will be parsing
for us, then maybe we can provide the code you are looking for.

--
Rick (MVP - Excel)


"ub" wrote in message
...
Thanks Paul

I tried using textiocolumns in vba for text "JASON AND PINTO AND DAVID AND
WEST". this text is in column A. What I am trying is to put each string in
different column staring from Column B
I wrote:
For i = 2 To 100
Range("a" & i).TextToColumns Destination:=Range("b" & i), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
other:=True, _
otherchar:="and"

But what I am getting is that column B has same text as column A. please
advise how to correct my code
" wrote:

Hi
Record a macro while you do this manually using Text to Columns
(within Data menu). Should give you a good start.
regards
Paul

On Oct 21, 1:39 pm, ub wrote:
Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

.

Split a text using VBA
 

If your text string always contain the same number of pieces then try the
below..

Sub Macro()
Dim strData As String, arrData As Variant
strData = "immm_JASON_PINTO_DAVID_WEST_1000011"
arrData = Split(strData, "_")
Range("A2") = arrData(1) & " " & arrData(2)
Range("B2") = arrData(3) & " " & arrData(4)
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"ub" wrote:

Hi Thanks for your help. I works.
Is there a way if I only want to extract "JASON PINTO" & "DAVID WEST" from
this string

"Jacob Skaria" wrote:

Try the below

Dim strData As String, arrData As Variant
strData = "immm_JASON_PINTO_DAVID_WEST_1000011"
arrData = Split(strData, "_")
Range("A2").Resize(1, UBound(arrData) + 1) = arrData

If this post helps click Yes
---------------
Jacob Skaria


"ub" wrote:

Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise

Split a text using VBA
 

Thanks to all for all the help
I am able to split my text as I need.



"Per Jessen" wrote:

Hi

With a text in a variable it can be done like this:

Sub aaa()
myarr = Split("immm_JASON_PINTO_DAVID_WEST_1000011", "_")
c = UBound(myarr) + 1
Range("A1").Resize(1, c) = myarr
End Sub

If your many cells with text to split, you should look at TextToColumns in
help.

Regards,
Per

"ub" skrev i meddelelsen
...
Hi
I have a text as follows:
immm_JASON_PINTO_DAVID_WEST_1000011
I want to split the text in different columns using vb code

Please advise


.