What do I miss in this Excel SEARCH() function to make it achieve what I want it to?

Question

I don't even know if this question belongs here or to another StackExchange site, so I beg your pardon if I'm mistakin'.

I'm normalizing addresses using Excel so that once done, I can insert them into the database that is ready for.

Let's take a few addresses on the fly (fictive):

1. 340       | 1st Street       | MyCity
2. 2-648     | 2nd Avenue       | AnotherCity
3. 102-648   | 2nd Avenue       | MyCity
4. 8A-605    | Wilson Boulevard | MyCity
5. A45C-103¼ | Hunting Crescent | MyCity

What I want to achieve with my Excel formula is as follows:

new Address() { StreetNumber=340, NumberSuffix=(NumberSuffixes)0, Street=streets.Single(s => s.Name == "1st Street"), City=cities.Single(c => c.Name == "MyCity") }
, new Address() { StreetNumber=648, NumberSuffix=(NumberSuffixes)0, UnitSuiteAppt="2", Street=streets.Single)s => s.Name == "2nd Avenue"), City=cities.Single(c => c.Name == "AnotherCity") }
, new Address() { StreetNumber=648, NumberSuffix=(NumberSuffixes)0, UniteSuiteAppt="102", Street=streets.Single(s => s.Name == "2nd Avenue"), City=cities.Single(c => c.Name == "MyCity") }
, new Address() { StreetNumber=605, NumberSuffix=(NumberSuffixes)0, UnitSuiteAppt="8A", Street=streets.Single(s => s.Name == "Wilson Boulevard"), City=cities.Single(c => c.Name == "MyCity") }
, new Address() { StreetNumber=103, NumberSuffix=(NumberSuffixes)1, UnitSuiteAppt="A45C", Street=streets.Single(s => s.Name == "Hunting Crescent"), City=cities.Single(c => c.Name == "MyCity") }

My actual Excel formula so far looks like the following:

=CONCATENATE("new Address() { StreetNumber=", RIGHT($D499, LEN($D499)-SEARCH("-",$D499)), ", NumberSuffix=(NumberSuffixes)0, UnitSuiteAppt=""", LEFT($D499, LEN($D499)-SEARCH("-",$D499)-2), """, Street = streetsOfShawinigan.Single(s => s.Name == """, $E499, """), City = cities.Single(c => c.Name == ""Shawinigan"") }")

The problem I'm facing is with this part of the formula:

[...]UnitSuiteAppt=""", LEFT($D499, LEN($D499)-SEARCH("-",$D499)-2), """[...]

It seems that SEARCH() doesn't work the same from one line to the other since it will work flawlessly for a given line, and not for the other. For instance, it could return:

for 2nd address:    [...]UnitSuiteAppt="2-"[...]
for third address:  [...]UnitSuiteAppt="1"[...]
for fourth address: [...]UnitSuiteAppt="8A"[...]
for fifth address:  [...]UnitSuiteAppt="A45"[...]

That is just to say that I want it to make the first part before the dash character the UnitSuiteAppt string value, while the part after is obligatory the StreetNumber. The first part, that apartment let's say, is of variable length. As such, I need to change the -2 for sometimes -1, and other times in favour of -3 which seems to depend on the length of the apartment part of the address, which makes sense, after all. My point of view is that SEARCH() shall always return the index of the "-", and as such, it doesn't seem to always work correctly, otherwise, it is me who didn't yet perfectly understood how it works, which I suspect seriously. ;-)

Any help is appreciated.

Thanks in advance for your precious time!

EDIT

I wished to include to complete generic solution that Rachel helped me find out with her very useful and precise answer. So here's the complete formula.

=CONCATENATE("new Address() { StreetNumber=", MID($D21, IFERROR(SEARCH("-", $D21), 0)+1, 5), ", NumberSuffix=(NumberSuffixes)0, ", IF(IFERROR(FIND("-", $D21, 1), "")="", "", CONCATENATE("UnitSuiteAppt=""", LEFT($D21, FIND("-", $D21, 1)-1), """, ")), "Street=streetsOfShawinigan.Single(s => s.Name == """, $E21, """), ", "City=cities.Single(c => c.Name == """, $F21, """) }")

Answer 1

To get the UnitSuiteAppt, use this: LEFT($D499,IFERROR(SEARCH("-",$D499),20)-1).

To get the StreeNumber, use this: MID($D499,IFERROR(SEARCH("-",$D499),20)+1,20).

Note: The value of 20 is put in as a default, maximum length of the value being searched.