I struggle with this too. A while back, I actually had a supervisor of mine suggest that I get "tested" to see if there was something wrong with me...maybe ADHD, she thought. I was driven and hyper-focused at times, and was scattered and aimless other times.
I ended up getting tested, more to put my mind at rest about it than anything, and I was not on the scale for ADHD at all. However, it highlighted that I did have some "impulsive" tendencies, and my motivation seemed to follow changes, not routine.
Not surprisingly, I've always been a better "starter" of things than "finisher". I haven't necessarily solved this challenge, but being more aware of my style and tendencies allows me to find jobs and other things that have some creative aspect to them. I love to develop and create new things, or innovate existing things. So, it makes me a good visionary and motivator...but then I need to hand the maintenance of the project over to someone who excels at that piece. Maybe this is why consulting is such a good fit for me?