Forum Discussion
Dash (-)is not recognized in cell
I imported a file formatted as a table. I have since saved it as a .txt and a .csv file as well. In column 1 is a word or phrase and a definition separated by a dash (-). When I try to use text-to-columns with the dash as the delimiter, it won't work. text-tpo-columns will work with any other delimiter. If I go into the cell and manually delete the dash and re-enter the dash, test-to-columns will work. I can not figure out what the problem is. I have also tried a trim formula and an iferror formula... no joy.
Any thoughts besides having to manually replace the dash in all 200 rows? Oh, and I tried find an d replace too.
thanx for any help
4 Replies
There are lot of characters which looks like dash “dash” Unicode Characters, Symbols & Entities Search | AmpWhat, you may compare UNICODE(45) and UNICODE(8208)
Try this, select the column you're having trouble with (or even the whole page). Go to the Home Tab and to Find & Select (or press F5 for some). The Go To Special dialog box should pop up. Click on Constants, and make sure all 4 are checked. Click ok. If any text or anything of that sort is hidden there, that should show you. You can do it again and look for Conditional formats you may have missed or formulas perhaps.
I think I had this issue once but it was with some sort of actual text and I had to open the formatting page and make a new format for it. Maybe like a date or something. If you have to make a new format cause there's nothing actually hidden there, just highlight that column and right click and go to Format Cells. Go down to Custom, and I just go all the way to the bottom of the Type and find one I never see used. Click on it, click Delete, and then you can create a new format of your choice.
So like I know normally date formats typically go MM/DD/YYY or YY/MM/DD in the US. But while I was doing excel training, they kept typing it as DD/MM/YY and it was throwing me off. So I created a new format for it. I hope that one of these helps you in this Delima.
I just re read your question and read it a bit differently this time. (dislexia is fun 🙄)
Again, another instance, cause it helps. I was using a formula to reference from one sheet to another. The formula was correct and it was pulling most all of the part number but not all. I went back and forth for a very long time trying to find different ways to do it or find out what I was doing wrong. But it wasn't me at all. Whomever first put the information in the first page I was referencing from, they had for some reason added extra spaces after the words/part numbers. Excel sees these but we can not unless we go in manually to check each one, or do a formula. Perhaps see if there is something extra sitting in the middle that you aren't seeing and it's blocking you from doing your action.?.
Maybe it is not a dash but a similar character. Check with UNICODE(). It should be 45.
