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

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

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

Please Login or Register  to view this content.

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

Yes

myText = Cells(1,1).Value

--
Regards,
Tom Ogilvy

"jackal2k6" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>