split address using split in vba

lezawang

Well-known Member
Joined
Mar 27, 2016
Messages
1,516
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
 

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
32,206
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,504
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,504
Office Version
2010
Platform
Windows
@ mohadin and brownbread... see the last three lines of what I posted in Message #4
 
Last edited:

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top