Category Archives for "Video"

4 Video Tip of the Week – Separating City, State Zip in Excel

In today's video, I show you how we use Excel to separate the City, State, and Zip Code and if you want the excel file I mention in the video, be sure to enter your name and email below and I will send you the excel file for free.

Get the Excel file here!


Hey, it's Scott Todd and in today's Excel video tip of the week, I wanted to share with you the one little tour, the way that we actually separate city, state and zip code when a county gives that to us, you know, you can't get a mailing list from a county and have it perfectly delivered to you, sometimes you need to kind of manipulate the data and sometimes they give you the city, state and zip code altogether and you stop to go back, and you know, kind of manipulate it into the separate columns. Stop, let me show you, let me show you what I'm talking about.

Okay, so here we are in Excel, you'll see that we have our list here, there's a one column that says city, state zip code and I need it in the three columns, so you know you could do the normal you know, manual labor of cutting, pasting, trim all that stuff. We're not going to do that, we're gonna work smart, what I'm gonna do is I'm gonna highlight these three columns right here because they need to insert three columns, so what I'm gonna do is, I'm gonna highlight them, I'm using the Mac so I'm gonna hit Ctrl Shift + and that's gonna add my three columns, I'm gonna go ahead and give them titles here, city, state and zip code. There we go, now what I'm gonna do is and I've made this so easy for you, I'm gonna give you basically this Excel template that you can use. I'm gonna tell you at the end how to get it it's free. I'm gonna tell you how to get it so that you can use this as you need to but check this out, I'm just gonna copy this down,

I'm gonna copy the city, state, zip code over here, I have put into this Excel spreadsheet this tab over here, and in this tab, you'll see that, you'll notice that in city, state, zip code I already have the formulas that you need right there, so what I'll do is I'm gonna come over here, I'm just gonna paste that -- “voila!” city, state, zip code is all cleaned! It's separated, but I still have some cleanup to do to it. So, let me show you what I'm gonna do now, what I'm gonna do, I just gotta take this column here, I'm gonna take these three columns, I'm going to copy them, I'm gonna come back over here, and I'm just gonna paste them right back over here into my my file that I'm working with, what I should have done is, I should have done spaced value. Let me show you what I did, I'm gonna clean this up, let me undo it, what I'm gonna do is I'm gonna come back over here, I'm going to right-click, do paste special values, and when I do that, you'll notice that the formula here is no longer the formula, is no longer here, so I can delete this combined file on my master worksheet and now I still need to do some cleanup to this.

What I need to do first, I need to first thing, I need to do is, I need to make sure that all of these zip codes are 0, you know have the proper digits for not four digits-- five digits right, and I've shown previously how to do that; but I'll just do it again real fast, I'm gonna come over here, right click Format Cells, come to Special, there's a couple ways you can do it, special zip code, or you can do it manually; let me come over here, I'm gonna do this again, it's custom one, two, three, four, five, boom - it's not working, let me investigate; okay, so the reason this is not working for me real fast is this it's telling me right here - like you see this little exclamation point when I click that? - it says this number is formatted as a text, so what I want to do is, I want to convert this to a number, now that I've converted to a number, I can come over here and do format cells. I'm just going to do special zip code, boom! - I have that.

I now have my five digit zip codes in there perfect. The last thing that I want to do though, is I want to clean this up because I don't want the commas at the end here, so what I'm gonna do here is, I'm gonna come over here, I'm just gonna do a find and in Excel module Find and Replace, so I'm just gonna do replace right here, and what I must tell it to do is, when it sees a comma in that column, just replace it with with a blank, so I don't have to put anything there, when I see a comma replace it with a blank and replace all. It's found 22 instances, closed; so, now what I have is, I have a properly formatted list with city, state zip code. City looks clean, state looks good, my zip codes are all five digits. I am ready to rock and roll! Very quick, very simple.

I hope that you found this tip, you know very helpful. I want to give you the Excel file, so what I want you to do is, I want you to go over to, Excel city altogether, and I'll put the link right below me right here. Go there, you can download the Excel file with the formulas, so you don't have to recreate the formulas. It's made easy for you, it's like having your own easy button and if you found this to be valuable please, leave your comments. Wherever you're watching this YouTube Facebook or on please, make sure that you leave comments and I will see you again soon, have a great week.


Are you Suffering from Head Trash?

[av_video src=’’ format=’16-9′ width=’16’ height=’9′]

Head Trash is the crap that floats around in your head, telling you that you can’t do something.  It’s the negative thoughts that seep into your brain stopping you dead in your tracks.  In this video I tell you exactly how to stop those thoughts and how to put an end to the head trash.

Here is the transcript for the video:

Hey, It’s Scott Todd from and man, I want to tell you, I’m out here enjoying the beautiful water of southwest Florida. Taking some family time and some time to rest and relax, and I wanted to tell you guys today about head trash. Because I know everybody experiences head trash, me included. And if you’re not familiar with what head trash is, well basically head trash is the negative thoughts that are seeping into your brain. It’s the thoughts that’s making you think that you can’t do something, or that you should stop taking the actions that will make a difference in your life to you and to your family.

When I first started out in real estate investing, man, everything in my head told me this was crazy. That it wasn’t for me. That it would never work for me and I shouldn’t do it. And the way that I found out, the way I overcame that head trash, is just to keep taking action to overcome what you’re thinking about.

So if your brain is telling you “That will never work for you.” or “This is impossible.” or “It’s not meant for me.”, just keep doing more of it and I think that what you’ll find is that that trash-talk, the “head trash”, goes away.

When I was also creating posting domination, man I got to a point where I thought to myself “Am I really able to provide value to the marketplace?” or “Can I really make this something that’s of value?”, and I started self-doubting myself and I slowed down. But then when I pushed through and did it anyway, it changes all of that head trash.

So if you’re experiencing head trash, negativity, negative thoughts, self-doubt, just keep taking action. I promise you it will make a big impact on your life if you keep going. Just tell it to “Shut up!”.

You can do it. And I’ll see you soon once you push through it.

Take care. Thanks for watching. Talk to you guys later. Bye bye.


Guess what the mailman brought me….

Let’s talk about Qualified Retirement Plans and profiting from Arbitrate on eBay and opening the mail to find out what offer letters the mailman brought me back while I was away at Land Geek Bootcamp.

Killing Time in the Phoenix AP after Landgeek Bootcamp

I was killing time in the Phoenix AP after The Land Geek Bootcamp.  I talked about Got-to-own-land-itis, yep, you know that feeling.  Never in your life have you wanted to own land, but now you have got to own it.

If you have a case of this syndrome, head over to and pick up some wholesale land.

The LandGeek Bootcamp (it was live)

In this Periscope Video, I broadcast live from The Land Geek Bootcamp in Scottsdale, AZ.  Check out what happens when land investors are at Land Geek Bootcamp.

The Scammer and Mickey Mouse College

Do you know Cindy Smith?

Cindy Smith who goes by the email address: [email protected]?

If you have Cindy on your buyers list, she will probably be sending you a fake check for some unknown property and then asking you for a refund.  As you probably know, Cindy is a scammer.

In this video I talk about my experience with Cindy and also answer a seller’s question, did I get so smart by attending Mickey Mouse College?

Are your a Real Estate Investor?

If so, I have been working for a while on a special program that you will not want to miss, sign up for free by providing your email address in the box above!

You don’t want to miss this!


The 30 Second Property Report

I hate creating property reports.

Yes, it’s nice to have something to send to a prospective buyer, however, they are pain to create.

I know what you’re thinking, why not have a VA produce them?  You’re right I should, however, I feel like I’m paying for something that is rarely used or asked for!

Enter my strategy, in this video you will find the WordPress Plug-in I use to create my property reports in 30 seconds or less.

What do you think, do you have a faster way?

Watch the video and let me know.

Also, if you would like more tips, tricks and motivation from a land investor, add your email address via the video and I will send you updates as I have more educational information like this!


Let’s talk about….a Few Key Land Investor Metrics

What land investor doesn’t like talking about, land?

In this video, we discuss how to find family members of deceased property owners and we also discuss a few of the metrics I keep track of and why.

Get an update on sales 20 and 21 on my road to 700 and help your favorite land investor reach 10,000 periscope hearts!