SUBSTITUTE formula giving unexpected result

I have an assignment for class that requires me to do certain things in excel and after working on it for around 15 hours or so didn't have any issues until I got to the SUBSTITUTE formula. Basically there is a table with an address column and we are to use the SUBSTITUTE formula to replace "Dr" with "Drive". I figured simple enough, this will be another one of those 30 second parts. 3 or 4 hours later I think I am losing it.

So in cell D18 it currently has "20511 Lake Forest Dr" and when I use the formula =SUBSTITUTE(D18,"Dr","Drive") it puts "0" in the cell instead of what I would expect. If I copy the entire address column to a blank column, say L for example, and use L18 in the formula instead of D18 in cell D18 it works, but I suspect this is not what the professor wants. Any help would be greatly appreciated.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Your post does not comply with Rule 7 of our Forum RULES. Please do not ignore Moderators' or Administrators' requests - note that this includes requests by senior members as well, if you are unclear about their request or instruction then send a private message to them asking for help. Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc

spursrule, I will restore your question once that user post a propper title.

Isn't the idea behind homework that you solve the problem yourself?

You've got a point - Have you tried the Evaluate formula feature to see what happens ? Otherwise post a small sample of your real data

Are you trying to enter SUBSTITUTE formula in cell D18 where is text that you want to change?

Originally Posted by gkeller81

So in cell D18 it currently has "20511 Lake Forest Dr" and when I use the formula =SUBSTITUTE(D18,"Dr","Drive") it puts "0" in the cell instead of what I would expect. If I copy the entire address column to a blank column, say L for example, and use L18 in the formula instead of D18 in cell D18 it works, but I suspect this is not what the professor wants. Any help would be greatly appreciated.

interesting... can you upload a sample workbook showing this happening.

I am not sure if that is a problem with 2010. I tried it in 2007 and it works fine.... I don't have 2010 here, but I or others can test for you to see if it is something with your spreadsheet or with your version.

2010 is OK

So where are you putting the formula....you can't put them in the same cell as the actual address you are trying to change.... and it looks like your addresses are in column F not D