Extract House Number from String

[DJDJDJDJ]

Hi,

I have a column with address details. I would like to separate the house number (always on the left) from the string. In some case, there might not be a house number, while in some other cases there might be a house and a flat number. I am only looking for the house number.

Table below, shows some sample data and the extracted columns I would like to have.

I have tried the following but I am not getting the intended results. Somehow it is pulling some letters as well.

=LEFT(S2,SUM(LEN(S2)-LEN(SUBSTITUTE(S2,{"0","1","2","3","4","5","6","7","8","9"},""))))

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sample Data[/TD]
[TD]House #[/TD]
[TD]Address[/TD]
[/TR]
[TR]
[TD] 123 Main Street[/TD]
[TD]123[/TD]
[TD]Main Street[/TD]
[/TR]
[TR]
[TD]7 Jones Blvd[/TD]
[TD]7[/TD]
[TD]Jones Blvd[/TD]
[/TR]
[TR]
[TD]Super St[/TD]
[TD][/TD]
[TD]Super St[/TD]
[/TR]
[TR]
[TD]Market Road Flat 2[/TD]
[TD][/TD]
[TD]Market Road Flat 2[/TD]
[/TR]
[TR]
[TD]4567 Elm St #7[/TD]
[TD]4567[/TD]
[TD]Elm St #7[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for any help.

 

[tyija1995]

Hey,

the following UDF will extract only numbers from a string, although this may cause problems such as the "4567 Elm St #7 " example as it would return "45677" - but feel free to give it a try if there are not many cases where there are additional numbers laying around.

Function NUMONLY(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        NUMONLY = .Replace(txt, "")
    End With
End Function

To use:
=NUMONLY(S2)

Where S2 is your string

 

[mse330]

How about this

Below formula to get the house number
=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,LEFT(A2,FIND(" ",A2)-1)+0,"")

Below formula to get the address​

=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,TRIM(MID(A2,FIND(" ",A2),99)),A2)

 

[mse330]

Same thing just to make more clear ...

Book1
	A	B	C
1	Sample Data	House #	Address
2	123 Main Street	123	Main Street
3	7 Jones Blvd	7	Jones Blvd
4	Super St		Super St
5	Market Road Flat 2		Market Road Flat 2
6	4567 Elm St #7	4567	Elm St #7
Sheet1

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,LEFT(A2,FIND(" ",A2)-1)+0,"")
C2	=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,TRIM(MID(A2,FIND(" ",A2),99)),A2)

 

[DJDJDJDJ]

Hey,

the following UDF will extract only numbers from a string, although this may cause problems such as the "4567 Elm St #7 " example as it would return "45677" - but feel free to give it a try if there are not many cases where there are additional numbers laying around.

Function NUMONLY(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        NUMONLY = .Replace(txt, "")
    End With
End Function

To use:
=NUMONLY(S2)

Where S2 is your string

How about this

Below formula to get the house number
=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,LEFT(A2,FIND(" ",A2)-1)+0,"")

Below formula to get the address​

=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,TRIM(MID(A2,FIND(" ",A2),99)),A2)

Same thing just to make more clear ...

	A	B	C
123 Main Street	Main Street
7 Jones Blvd	Jones Blvd
Super St		Super St
Market Road Flat 2		Market Road Flat 2
4567 Elm St #7	Elm St #7

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Sample Data[/TD]
[TD="bgcolor: #FFFF00"]House #[/TD]
[TD="bgcolor: #FFFF00"]Address[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]123[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4567[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,LEFT(A2,FIND(" ",A2)-1)+0,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(ISNUMBER(LEFT(A2,FIND(" ",A2)-1)+0)=TRUE,TRIM(MID(A2,FIND(" ",A2),99)),A2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you guys. You are all awesome. I ended up using the formula instead of the script. Only because I need to learn a bit more about scripting. This script, however gives me a good start.

Much appreciated.

Best.

 

[DJDJDJDJ]

By the way, any good source to learn about Regexp?

Thanks.

 

[tyija1995]

By the way, any good source to learn about Regexp?

Thanks.

I'd probably look at something like this:
https://www.experts-exchange.com/ar...asic-for-Applications-and-Visual-Basic-6.html

there are some built in patterns such as the one I used "\D" which refers to non-numerical values, then I basically replaced all of the non-numerical values with blank, thus leaving numerical values only. It's pretty cool!

 

[jtakw]

Hi,

Also try this:

Book1
	A	B	C
1	Sample Data	House #	Address
2	123 Main Street	123	Main Street
3	7 Jones Blvd	7	Jones Blvd
4	Super St		Super St
5	Market Road Flat 2		Market Road Flat 2
6	4567 Elm St #7	4567	Elm St #7
7	7 Elm St #7	7	Elm St #7
Sheet672

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(LEFT(A2)+0),LEFT(A2,FIND(" ",A2))+0,"")
C2	=TRIM(SUBSTITUTE(A2,B2,"",1))

Formulas copied down.

 

[DJDJDJDJ]

I'd probably look at something like this:
https://www.experts-exchange.com/ar...asic-for-Applications-and-Visual-Basic-6.html

there are some built in patterns such as the one I used "\D" which refers to non-numerical values, then I basically replaced all of the non-numerical values with blank, thus leaving numerical values only. It's pretty cool!

Thank you very much.

Hi,

Also try this:

	A	B	C
Sample Data	House #	Address
123 Main Street	Main Street
7 Jones Blvd	Jones Blvd
Super St		Super St
Market Road Flat 2		Market Road Flat 2
4567 Elm St #7	Elm St #7
7 Elm St #7	Elm St #7

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]123[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]4567[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]7[/TD]

</tbody>

Sheet672​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(ISNUMBER(LEFT(A2)+0),LEFT(A2,FIND(" ",A2))+0,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(A2,B2,"",1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formulas copied down.

Thank you.

 

[jtakw]

You're welcome, thanks for the feedback.