Yes, you will.
However, it's not just the processor that will increase your speed. With the new processor, you will most likely need a new motherboard (better chipset), new memory (DDR2 versus DDR), a new graphics card (PCI-E), and possibly new hard drives (SATA). All of these put together will increase the speed of your system ten fold.
However, if it was possible to just stick the Core 2 Duo in your current system, you would still see a huge performance gain over the old Northwood. Though you may be only using single applications, dual cores help a ton. How many times have you played a game, and had to open up a browser really quick? Keep in mind that your computer also has many background applications that eat up processor resources as well.