Splitting the value of a textbox into 2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

red6000 - 31 Jan 2006 20:44 GMT

Hi,

I have a VBA macro in Word XP that has 3 textboxs

TextBox1 will contain a name, both first and last name

What I want to do is be able to split the value in TextBox1 into 2 so that
TextBox2 contains the first name (ie the text up to the space in TextBox1)
and TextBox3 contains the lastname (ie the text in TextBox1 after the
space).

Any clues on how to achieve this? I assume I will need to use some form of
length function and a function to find the space?

Thanks

Reply to this Message

Jezebel - 31 Jan 2006 21:00 GMT

Some combination of Instr(), Left$(), and Mid$()

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

red6000 - 31 Jan 2006 21:18 GMT

Thanks, sorted it with:

Dim SearchString, SearchChar, MyPos, First, Last

SearchString = TextBox1.Value
SearchChar = " "

MyPos = InStr(1, SearchString, SearchChar, 1)
MyPos = MyPos - 1

First = Mid(SearchString, 1, MyPos)

MyPos = MyPos + 2

Last = Mid(SearchString, MyPos)

TextBox2.Value = First
TextBox3.Value = Last

> Some combination of Instr(), Left$(), and Mid$()
>
[quoted text clipped - 13 lines]
>>
>> Thanks

Reply to this Message

Jezebel - 31 Jan 2006 22:22 GMT

Split() is another possibility that might be better in this case.

1. Add some error-handling in case there is no space at all.

2. Use Trim$() so you don't need to guess how many spaces there are

3. Use the string versions of the sub-string functions (Mid$() rather than
Mid()) -- they are about 1000 times faster.

4. Declare your variables by type. Variants are slow and lead to bugs.

> Thanks, sorted it with:
>
[quoted text clipped - 32 lines]
>>>
>>> Thanks

Reply to this Message

Dave Lett - 31 Jan 2006 21:16 GMT

You can do that any number of ways:

ONE:
Dim sName As String
Dim sFirst As String
Dim sLast As String
Dim iPos As Integer
sName = "Your Name"

iPos = InStr(1, sName, " ") - 1
sFirst = Left(sName, iPos)
sLast = Right(sName, iPos)
MsgBox "First: " & sFirst & vbCrLf & _
"Last: " & sLast

TWO:
Dim sName As String
Dim aName As Variant
sName = "Your Name"
aName = Split(sName, " ")
MsgBox "First: " & aName(0) & vbCrLf & _
"Last: " & aName(1)

HTH,
Dave

> Hi,
>
[quoted text clipped - 11 lines]
>
> Thanks

Reply to this Message

Rate this thread: