Both my kids play in football teams. I think it has been really good for them, especially my daughter because she's got to meet other girls who are like her. All her school friends are not interested in sport and are more into make up and doing their nails etc.
They've both made new friends they would never have met through their teams and I am lucky that the parents of both teams have been really welcoming and friendly.
I think children can get the same benefits from other interests though, it could be other sports or chess club or whatever. I wouldn't worry too much if he's not too bothered.
I do really sympathise with you feeling guilty about him missing out because you don't drive though. I can't afford to drive and I often worry about the same thing with my children when I see other people driving on camping holidays or days out at the weekends and school holidays. There are some places you just can't get to on public transport.