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"},""))))

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

<tbody>
</tbody>

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

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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) <tbody> </tbody>

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet672​

Worksheet Formulas Cell Formula B2 =IF(ISNUMBER(LEFT(A2)+0),LEFT(A2,FIND(" ",A2))+0,"") C2 =TRIM(SUBSTITUTE(A2,B2,"",1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Formulas copied down.

Thank you.

 

[jtakw]

You're welcome, thanks for the feedback.