I have a spreadsheet that was imported from other software. For some reason it added an extra / in the date field. For example it says 12/22//1971 instead of 12/22/1971 I need to remove the extra / before I can upload it into another program. Please help.
with this case You can use formula =SUBSTITUTE(a1,"/","",3)
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
You can use a formula in an adjacent cell, like:
=REPLACE(A1,6,1,"")+0
copied down (you may need to format as date).
Then you can copy and paste special >> Values this over the original.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
You can try this formula , where A2 contains your data.
=SUBSTITUTE(A2,"//","/",1)
Sorry for 1-upping all the clever answers so far, but this is quicker and easier!
Highlight all of the affected cells, hit Ctrl+F, go to the replace tab;
Find what: //
Replace with: /
Dim Prawny As Newb
Forgive us formula fanatics... sometimes the simple things in life elude us
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks