Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORD-DELIMITED string vba macro for excel/word
Greetings everyone, I've been working on this for a while without success. How can I split the following type of text into word-delimited tokens, so that each token gets created when upon the next "Full Name" string, and each token gets placed on a new line, for example this type of text: FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL NAME:XX SURNAME:X FIRST NAME:X FUNCTION:X FULL NAME:XXX SURNAME:XX FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789 Being split into this one? FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL NAME:XX SURNAME:X FIRST NAME:X FUNCTION:X FULL NAME:XXX SURNAME:XX FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789 The features available only have character delimiters, and I would be really grateful if you could help me with a macro to achieve the above re-formatting. Note that each token has a variable width, begins with "Full Name" and ends with the piece of text after "Function". I await your reply! THanks -- jackal2k6 ------------------------------------------------------------------------ jackal2k6's Profile: http://www.excelforum.com/member.php...o&userid=29872 View this thread: http://www.excelforum.com/showthread...hreadid=495746 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORD-DELIMITED string vba macro for excel/word
Hi,
If I have understood your needs correctly, the following will split the string and place each "FULL NAME" string in a new row in column A. I have taken the string to be literally as you posted i.e. this as a SINGLE text string (in MyText in my example) FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL NAME:XX SURNAME:X FIRST NAME:X FUNCTION:X FULL NAME:XXX SURNAME:XX FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789 Sub SplitTextX() Dim v As Variant Mytext = "FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL " & _ "NAME:XX SURNAME:X FIRST :X FUNCTION:X FULL NAME:XXX SURNAME:XX" & _ "FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789" v = split(Mytext, "FULL NAME") For i = 1 To UBound(v) Cells(i , 1) = "FULL NAME" & v(i) Next i End Sub "jackal2k6" wrote: Greetings everyone, I've been working on this for a while without success. How can I split the following type of text into word-delimited tokens, so that each token gets created when upon the next "Full Name" string, and each token gets placed on a new line, for example this type of text: FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL NAME:XX SURNAME:X FIRST NAME:X FUNCTION:X FULL NAME:XXX SURNAME:XX FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789 Being split into this one? FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL NAME:XX SURNAME:X FIRST NAME:X FUNCTION:X FULL NAME:XXX SURNAME:XX FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789 The features available only have character delimiters, and I would be really grateful if you could help me with a macro to achieve the above re-formatting. Note that each token has a variable width, begins with "Full Name" and ends with the piece of text after "Function". I await your reply! THanks -- jackal2k6 ------------------------------------------------------------------------ jackal2k6's Profile: http://www.excelforum.com/member.php...o&userid=29872 View this thread: http://www.excelforum.com/showthread...hreadid=495746 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORD-DELIMITED string vba macro for excel/word
Hi Toppers, it works perfectly with the myText value you assigned to it - how would it need to be modified if I want to do the split for a single long string that's already in a cell, say A1? would the conversion be Code: -------------------- myText = Cells (1,1)? -------------------- Thanks! Sub SplitTextX() Dim v As Variant Mytext = "FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL" & _ "NAME:XX SURNAME:X FIRST :X FUNCTION:X FULL NAME:XXX SURNAME:XX" & _ "FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789" v = Split(Mytext, "FULL NAME:") For i = 1 To UBound(v) Cells(i, 1) = "FULL NAME:" & v(i) Next i End Sub -- jackal2k6 ------------------------------------------------------------------------ jackal2k6's Profile: http://www.excelforum.com/member.php...o&userid=29872 View this thread: http://www.excelforum.com/showthread...hreadid=495746 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
WORD-DELIMITED string vba macro for excel/word
Yes
myText = Cells(1,1).Value -- Regards, Tom Ogilvy "jackal2k6" wrote in message ... Hi Toppers, it works perfectly with the myText value you assigned to it - how would it need to be modified if I want to do the split for a single long string that's already in a cell, say A1? would the conversion be Code: -------------------- myText = Cells (1,1)? -------------------- Thanks! Sub SplitTextX() Dim v As Variant Mytext = "FULL NAME: XXXXXX SURNAME: XX FIRST NAME: XXXX FUNCTION: XXX FULL" & _ "NAME:XX SURNAME:X FIRST :X FUNCTION:X FULL NAME:XXX SURNAME:XX" & _ "FIRST NAME:X FUNCTION:X FULL NAME: 12345 SURNAME: 45 FIRST NAME: 123 FUNCTION: 6789" v = Split(Mytext, "FULL NAME:") For i = 1 To UBound(v) Cells(i, 1) = "FULL NAME:" & v(i) Next i End Sub -- jackal2k6 ------------------------------------------------------------------------ jackal2k6's Profile: http://www.excelforum.com/member.php...o&userid=29872 View this thread: http://www.excelforum.com/showthread...hreadid=495746 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how we find specific word from string in excel and compaire it. | Excel Worksheet Functions | |||
Frequency of a word string in an excel database | Excel Discussion (Misc queries) | |||
Pull last word from a text string in Excel | Excel Worksheet Functions | |||
identify a word in a string then change the format of that word | Excel Programming | |||
Use Word VBA string in Excel macro? | Excel Programming |