- 2,340
- United Kingdom
- half_sourly
I've found a way. It is long-winded, but if you have a big dataset will be worth it. I think the issue is that the source data has hidden special characters - save as .txt and open in excel and you'll see what I mean.Man, I think I've tried it all lol. The value function just gives me #VALUE error. Trying to add +0 or *1 gives #VALUE error. Copying - paste special value & addition gives a blank. Text to column still returns text string. Puzzling!
To solve it:
- Select the data from skidmark's table
- paste into Word (I know, but the word to excel thing has worked for me in the past on other problems, so thought I'd try it)
- In Word it will be a table. Select the table and convert to text (via Layout ribbon button)
- Select the data in Word and paste into Notepad (don't save as .txt from Word as some ? characters remain)
- Save the Notepad file as .txt with UTF-8 (the default; others might work but I didn't test)
- Open in excel with default settings (delimited; MS-DOS (PC-8) on mine).
Last edited: