The lens does not actually turn into a different focal length, since that's a real, physical property of the optics that can't be changed without more optics. So from that point of view, the answer is a definitive no.

However, when you get to the question of is it effectively the same in terms of magnification, the answer is "pretty much, given some assumptions."

A key assumption is that you're printing at the same size. That means: you're increasing the magnification of the image from the smaller sensor. If you print at sizes different by the same ratio of the crop factor, you get exactly the same result as if you just took a full-frame photo, printed large, and then cropped out the middle.

So, if you print your full-frame picture at 12×9", and print your crop-factor picture at 7.5×5.6" (for Canon; 8×6" for others, or 6×4.5", or whatever), and then chop down the full-frame print to match, they'll be roughly the same.

"Roughly" comes in because, of course, the actual sensors won't be equivalent in image quality. (The crop-factor print may have larger resolution, but from denser photosites, depending on the technology generation used in each camera.)

Blowing up that cropped image — either from the full-frame cropped print, or from the cropped sensor — has two effects which are very like changing the focal length. And these two things are the most visible effects of changing focal length — field of view, as you've noted; and depth of field, which changes (almost) exactly as if you'd adjusted the f-stop by the amount of the crop.

If you've ever used a point and shoot camera with "digital zoom", that's what's actually going on. It's cropping the photo and then expanding it. From a practical point of view, zoom is indistinguishable from cropping. But of course, that raises the spectre of decreased image quality — we all know that digital zoom can be awful. The answer is simply that sensor technology is really very good, and amazing, excellent results can be produced at even large print sizes even with a 1.5 or 1.6× crop — but if you do want to go larger with your prints, eventually you need a larger sensor. And, equivalently, if you want to zoom in more, you can do that with more cropping, but eventually, you need actual higher-focal-length glass.

Note that this doesn't address macro shooting. I don't really do any of that, so I'll let someone else handle that aspect of the question — which I think is well-addressed here: Does a camera's crop factor apply to the magnification of macro shots?