Remove Apt Number and street from address

[mjservingjesus]

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.

 

[TheCman81]

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
	A	B
1	Dilger Ave #61	Dilger
2	Dilger Ave Apt 61	Dilger
3	Dilger Ave Lot 61	Dilger
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND(" ",A1)-1)

 

[Jordan213]

Google how to perform "text to columns", use space as your delimiter, delete extraneous columns. Badabing.

 

[mjservingjesus]

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

 

[TheCman81]

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

 

[mahonejohn]

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

 

[REB]

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