Excel Guides

Extracting Street Numbers from an Address in Excel

There are a few different ways that you can extract street numbers from an address in Excel. One way is to use the LEFT function. The LEFT function allows you to extract a certain number of characters from the left side of a string of text. For example, if you have an address in cell A1 and you want to extract the street number, you could use the following formula:

=LEFT(A1,FIND(" ",A1)-1)

This formula will extract the characters from the beginning of the string up until the first space. So, if the address in cell A1 is "123 Main Street", the result of this formula would be "123".

Another way to extract street numbers from an address is to use the MID function. The MID function allows you to extract a certain number of characters from any position in a string of text. For example, if you have an address in cell A1 and you want to extract the street number, you could use the following formula:

=MID(A1,1,FIND(" ",A1)-1)

This formula will extract the characters from the beginning of the string up until the first space. So, if the address in cell A1 is "123 Main Street", the result of this formula would be "123".

If you have an address in which the street number is not at the beginning, you can still use the MID function. You just need to specify the starting position for extraction. For example, if you have an address in cell A1 and you want to extract the street number, but it is not at the beginning of the string, you could use

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.