Let's start with a vacuum, I will write |0000......> as |0>.
$$ \psi^+ (x) |0\rangle = \sum_k e^{-ikx} a^+ (k) |0\rangle $$

Qns: What happens if you superpose/add up state vectors corresponding to a particle of momentum k and you add up with coefficient e^{-ikx}?
$$ \sum_k e^{-ikx} a^+ (k) |0\rangle $$ where a^{+} is a creation operator

Ans: The ans is it is a position state. It is a state with a definite position. What is a position? x. If you add up momentum states with e^{-ikx}, that gives you a position state located at particle x. This is one particle quantum mechanics.

Source: Starts at 40:21

I am lost at this part whereby how multiplying e^{-ikx} with a^{+}(k) gives the position state?

You need to look at the Fourier expansion of a delta function which is the wave function for a particle localized at a point position.
Any position vs momentum picture transformation is going to be a Fourier expansion of the other. Here you simply have it applied to operators in the field theory.