WORD-DELIMITED string vba macro for excel/word

[jackal2k6]

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

 

[Guest]

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]

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

 

[Tom Ogilvy]

Yes

myText = Cells(1,1).Value