split address using split in vba

[lezawang]

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

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

 

[mohadin]

hi when split text you get an array
in your case X(0) x(10) x(2)

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]

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]

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.

 

[mohadin]

Or

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]

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:

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

 

[Rick Rothstein]

@ mohadin and brownbread... see the last three lines of what I posted in Message #4

 

[mohadin]

@ mohadin and brownbread... see the last three lines of what I posted in Message #4

Thanks Mr. Rothstein
For Valuable bit of Info
You are great
Best Regards