Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Split | New Users to Excel | |||
Split Text | Excel Programming | |||
Split text without using data-text to columns | Excel Discussion (Misc queries) | |||
text cells end page how split to next. Text lost! | Excel Discussion (Misc queries) | |||
How to split text? | Excel Programming |