# Remove Apt Number and street from address

#### mjservingjesus

##### New Member
Hello, I found out a way to get rid of the street number from an address, but I would like the street and apartment number removed. Right now I have various formats:
Dilger Ave #61
Dilger Ave Apt 61
Dilger Ave Lot 61

I would like simply "Dilger".

Is there a way to do this? I am not extremely familiar with Excel forumlas so any necessary explanation would be much appreciated.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Can you supply some more sample data to work with?

Is the text you want to extract always at the start? Is it always 1 word?

Based on your sample data it would be something like this

Excel 2010
AB
1Dilger Ave #61Dilger
2Dilger Ave Apt 61Dilger
3Dilger Ave Lot 61Dilger
Sheet1
Cell Formulas
RangeFormula
B1=LEFT(A1,FIND(" ",A1)-1)

TheCMan, that was actually all I needed! Thank you!

No problem, if you need any further help, please don't hesitate to ask

Nice information I get for find street address only need to learn this simple with Excel formulas.
real estate training

123 Main St Apt 123 =RemoveSuite(A2) Yields: 123 Main St
'*****************************************************************************************************
Function RemoveSuite(Txt As String) As String
On Error Resume Next

If WorksheetFunction.Find("Apt", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("Apt", Txt), 255))))
End If

If WorksheetFunction.Find("Unit", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("Unit", Txt), 255))))
End If

If WorksheetFunction.Find("apt", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("apt", Txt), 255))))
End If

If WorksheetFunction.Find("unit", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("unit", Txt), 255))))
End If

If WorksheetFunction.Find("Suite", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("Suite", Txt), 255))))
End If

If WorksheetFunction.Find("suite", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("suite", Txt), 255))))
End If

If WorksheetFunction.Find("#", Txt) > 0 Then
RemoveSuite = Left(Txt, (Len(Txt) - Len(Mid(Txt, WorksheetFunction.Find("#", Txt), 255))))
End If

If RemoveSuite = "" Then
RemoveSuite = Txt
End If

On Error GoTo 0
endmacro:

End Function

Replies
4
Views
409
Replies
27
Views
803
Replies
2
Views
335
Replies
8
Views
2K
Replies
2
Views
342

1,203,269
Messages
6,054,471
Members
444,727
Latest member
Mayank Sharma

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back