Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 241 AND i.igate IN ('usahud','cahud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 241 AND i.igate IN ('usahud','cahud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 241 AND i.igate IN ('usahud','cahud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'cahud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

18301 Rd 256
Strathmore, CA 93267
Anthony Hargus with Downtown Realty, original listing - (559) 615-0215

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $1,499,000
Beds: 4
Baths: Full: 4
Status: Active
SqFt: 5853 Square Feet
Agency: Downtown Realty
Agency Phone: (559) 615-0215

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: 40 acre(s)
Garage: Garage
Property Type: Residential-Single Family Residence
Year Built: 1994
Notes: Introducing an unparalleled estate nestled on a serene hillside, spanning just over 40 acres of exquisite landscape. This extraordinary property boasts a harmonious blend of eclectic charm, impeccable views, and utmost privacy. From the moment you pass through the gated entry at the driveway, you will notice how this captivating estate exudes meticulous attention to detail and care.<br /> <br /> Step into the grandeur of the entry room, where a striking rotunda and large staircase welcomes you with a sense of luxury and sophistication. The formal living room adjacent to the entry radiates warmth, adorned with a marble fireplace and custom high ceilings, creating an inviting ambiance perfect for entertaining guests or enjoying family moments. Ascend the elegant white iron spiral staircase to discover a second-floor nook offering a captivating view of the space below.<br /> <br /> The first floor unfolds with an array of well-appointed spaces, including a formal entry room, library, kitchen, family room, and a spacious computer/office area, seamlessly blending practicality with elegance. As you ascend to the second level, each of the three bedrooms boasts its own unique character and meticulous detailing. The master suite epitomizes opulence, featuring an expansive layout that offers endless possibilities, complemented by a more amazing views of the mountains from to off room balconies. <br /> <br /> Beyond the main residence, the estate encompasses additional remarkable amenities, including a detached 4-car garage with a guest house on the second floor, a 40x60 shop, and a new domestic well. <br /> <br /> Step into the backyard retreat and immerse yourself in the tranquility of breath taking views encompassing the rolling hills and majestic Sierra Nevada mountain range whether lounging poolside or exploring the vast expanse of the estate, this privately situated haven offers a sanctuary of unparalleled beauty and serenity.
MlsNumber: --
ListingId: 227849


Listing Provided By: Downtown Realty, original listing
Phone: (559) 542-4334
Office Phone: (559) 615-0215
Agent Name: Anthony Hargus
Disclaimer: Copyright © 2024 Tulare County MLS. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App