split address using split in vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,565
Hi
I want to ask user to enter address for example 100 main street, and then my code will split the number and street name.

I tried the following but got error message. I am thinking of array must be use or do you think there is easier way to do that

thank you very much

Code:
Sub myfirst()
    Dim x As String
    x = InputBox("enter your address")
    MsgBox Split(x)
End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
hi when split text you get an array
in your case X(0) x(10) x(2)
Code:
Sub myfirst()
    Dim x As String
    x = InputBox("enter your address")
    MsgBox Split(x)(0)
    MsgBox Split(x)(1)
    MsgBox Split(x)(2)
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,846
Office Version
365
Platform
Windows
You cannot display an array in the msgbox like that.
If you go back over your previous posts, you will see that, as you have already asked this question in past.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
Code:
Sub myfirst()
    Dim x As String
    x = InputBox("enter your address")
    MsgBox Split(x)
End Sub
The Split function returns an array so you can index it directly. The first element returned by the Split function always has an index of 0. So this will display the number...

MsgBox Split(x)(0)

You can get the remainder of the address by making use of the optional arguments the the Split function can take...

MsgBox Split(x, , 2)(1)

The 2 in that argument position tells Split to create an array of two indexes (0 and 1) where the 0 index is the text before the first space and the 1 index is everything after that first space.
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Or
Code:
Sub myfirst()
    Dim x As String
    x = InputBox("enter your address")
    s = Split(x)
    MsgBox s(0) & vbLf & s(1) & vbLf & s(2)
End Sub
 

brownbread

New Member
Joined
Jun 21, 2010
Messages
20
Hi there
You can avoid using an array variable. However, splitting does create an array, and so you need to tell Excel which "segment" you need.

Since we just want "100" and "main street", but the address line can include multiple spaces (100[]main[]street), this creates a challenge for us: We could either capture the whole array and then "reassemble" the bits we need... OR we could just capture the first segment, and then remove that segment from the rest of the original string. Let's do that:

Code:
[FONT=courier new]Sub myfirst()
    Dim x As String
    Dim addrNumber As String 'it may contain a letter, e.g. "23A"
    Dim addrStreet As String
    
    x = InputBox("enter your address", "Address", "100 main street")
    
    addrNumber = Split(x, " ")(0)
    addrStreet = Mid(x, Len(addrNumber) + 2, Len(x) - Len(addrNumber) - 1)
    
    MsgBox addrNumber & vbNewLine & addrStreet
End Sub[/FONT]
Result:
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,923
Office Version
2010
Platform
Windows
@ mohadin and brownbread... see the last three lines of what I posted in Message #4
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,329
Messages
5,467,974
Members
406,561
Latest member
Grappledog

This Week's Hot Topics

Top