Help with disecting text...

[Tecnik]

Hi there,

Please can someone help me with this query regarding splitting a string.
In Applescript I can set something called a Text Item Delimiter and wondered if the same were possible with VBA?

Let's say I've got the string:-

cow,sheep,pig

and I set the Text Item Delimiter to a comma, the following text items would be available.

1 = cow
2 = sheep
3 = pig

Can I do the same with VBA?

Thanks in advance,

Nick

[mvidas]

Hi Nick,

Yep! Using the Split() function you can do this, though it results in a zero-based array (0=cow,1=sheep,2=pig). See how it works and get the items using:Sub NickSplitExample()
Dim OrigStr As String, vSplitItems() As String, i As Long
OrigStr = "cow,sheep,pig"
vSplitItems = Split(OrigStr, ",")
For i = 0 To UBound(vSplitItems)
MsgBox "vSplitItems(" & i & ") = " & vSplitItems(i)
Next
End SubMatt

[Tecnik]

Hi Matt,

Thanks for your help with this query and for the code which was really useful.
Shortly after posting I found the Split function however I was having problems when trying to get a count, your code has made things a little clearer, thank you.

My aim was to put together a macro that would create a dropdown from a text selection, here's where I'm upto:-

Sub NickSplitExample()
Dim OrigStr As String, vSplitItems() As String, i As Long

OrigStr = Selection
vSplitItems = Split(OrigStr, "/")

Set fField = ActiveDocument.FormFields.Add( _
Range:=ActiveDocument.Range(Start:=0, End:=0), _
Type:=wdFieldFormDropDown)

With fField
.Name = "Animals"
With .dropdown.ListEntries
For i = 0 To UBound(vSplitItems)
.Add Name:=Trim(vSplitItems(i))
Next
End With
End With
End Sub

It's pretty much there, apart from getting rid of a slight niggle with paragraph returns. If there's a paragraph return at the end of the text selection the Trim function doesn't remove it. Am I right in thinking the Trim function removes spaces only? If so how can I get rid of things like paragraph returns?

Thanks again for the help Matt,

Regards,

Nick

[mdmackillop]

.Add Name:=Replace(Trim(vSplitItems(i)), Chr(13), "")

[Tecnik]

Thanks for the help MD and for the code snippet, that's great.

Regards,

Nick :)



EDIT: Further to the above, I thought I'd add the final routine, it may be useful to someone one day.

The routine makes a dropdown from user selected text on a page. The selected text would be in this format:-

red / green / blue / yellow

The separator and styling can be changed as required.

Regards,

Nick

P.S. I think the next little addition will be to change the routine so instead of it working with a selection it does the whole document in one go, looking for underlined text.

Here's the code:-
Sub dropdown()
Application.ScreenUpdating = False
Dim OrigStr As String, vSplitItems() As String, i As Long

OrigStr = Selection

If Selection.Font.underline = wdUnderlineNone Then
Selection.Font.underline = wdUnderlineSingle
Else
Selection.Font.underline = wdUnderlineNone
End If

Selection.TypeBackspace

vSplitItems = Split(OrigStr, "/")

Set fField = ActiveDocument.FormFields.Add( _
Range:=Selection.Range, _
Type:=wdFieldFormDropDown)

With fField
.Name = "Animals"
With .dropdown.ListEntries
For i = 0 To UBound(vSplitItems)
.Add Name:=Replace(Trim(vSplitItems(i)), Chr(13), "")
Next
End With
End With

Selection.TypeText Text:=" "

Application.ScreenUpdating = True
End Sub