Grounding and neutral in a well pump shed
Current flows in loops. Between hot and hot; or between hot and neutral.  But always a loop.
Wayward current wants to get back to SOURCE - not earth.  For human-generated artificial electricity, wayward hot current wants to get back to NEUTRAL (or an opposing hot) not ground.  It has no interest in ground, except that there’s a neutral-ground bond back at the main panel/service point.
Ground and neutral have three completely separate jobs:

Neutral returns normal everyday current.  This is the only wire current should move on (Beside the hots).
Ground returns human-generated fault current to source. That means the ground-neutral bond back at the main panel. This requires it to flow hundreds of amps, which is why it must be a competent wire.
Ground returns naturally made electricity (lightning, ESD) back to its source, which is earth.  A ground wire a long distance to another building’s main panel is a very bad choice, because lighting will incinerate this wire, and it will create a deadly voltage gradient between the buildings.  For this problem, you need a ground rod.

The dirt you’re driving ground rods into, is just dirt. It conducts electricity about as well as you’d expect dirt to.  Not nearly well enough to handle any human-made electricity - not as normal return current, nor as fault current.
The neutral wire may be fixable
I don’t blame you; most people struggling with a non-conducting wire go straight to the assumption that the wire is broken somewhere in the inaccessible parts of the cable.  Actually, that’s rare.  A single wire doesn’t randomly take damage like that (without affecting the others).
As such, I would very, very carefully inspect the neutral wire everywhere (it is not inside the cable sheath).  You may find a break or loose connection that you hadn’t seen before, and that is fixable.
Not least, where the neutral lands on the neutral bar in the panel - nobody checks that lol.
Lights and outlets are different problems
These days, lights are easy - just go shopping for modern LED fixtures, and many of them will run 120/240V multi-voltage.  If your lights are fluorescent tubes, things are even easier - change to a modern electronic ballast. Almost all are multi-voltage rated... so aside from efficiency, coldstart, zero buzz, zero flicker and better light than LED... you can also run them on 240V!
Receptacle outlets, however, are a PitA because we never know what someone is going to plug into them.  So you must wildly oversize the current provisioning.  This is not an easy problem to solve, except by saying “I don’t need receps out here that badly, how about we skip it?”
But there’s another problem with receptacles.  Code is clear that if more than 50% of your circuit ampacity is used by hardwired loads, you cannot have any receps at all.  So it was surprising to me to hear a well pump circuit with receptacles.   Also, common receps can only be on 15A or 20A circuits.  If you really wanted to do that thing, you could fit a subpanel in the shed, at which point the house run is a “feeder” and the well gets its own breaker and circuit... but obviously, subpanels really need neutral wires.  So fixing that would be a prerequisite.
There’s no such thing as fixing just a neutral wire, unless it is an individual wire run in a conduit.  If it is buried cable, you really need to bet all the marbles that it’s a problem at one of the ends.  But that is a fairly good bet.