A Google search led me to Chip Pearson's excellent text file import code.
One of the columns in my .txt file holds phone numbers, some of which users have 'helpfully' spaced with hyphens. On import Excel appears to be interpreting the hyphen as a minus sign and so it's doing maths on the phone numbers
Any clues how I can specify that the .txt file is imported into cells formatted as text, so that the file is read 'as is'?
Ken
PS there's no rush to answer as I'm leaving work in 10 mins and then I'm on holiday all next week
import txt file ot cells formated as text
-
- Panoramic Lounger
- Posts: 8176
- Joined: 25 Jan 2010, 09:09
- Location: retirement
-
- Administrator
- Posts: 12612
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: import txt file ot cells formated as text
Have a great holiday Ken - will you be taking your bike from Yorkshire to London?
StuartR
-
- Panoramic Lounger
- Posts: 8176
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: import txt file ot cells formated as text
No, taxi to MAN, plane to Austria.StuartR wrote:will you be taking your bike from Yorkshire to London?
Ken
PS any word on the VBA problem?
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: import txt file ot cells formated as text
Don't right-click the text file in Windows Explorer and select Open With > Microsoft Excel. This causes Excel to be too smart.
Use the File > Open dialog in Excel to open the file. This will start the Text Import Wizard, in which you can specify that a column should be treated as text.
Happy holiday!
Use the File > Open dialog in Excel to open the file. This will start the Text Import Wizard, in which you can specify that a column should be treated as text.
Happy holiday!
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8176
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: import txt file ot cells formated as text
Sorry, didn't make myself clear. I know about the text import wizard but I want to import using VBA. Chip Pearson's code does what I want but I can't see how to tweak the code to ensure the columns end up formatted as text and not general.HansV wrote:This will start the Text Import Wizard, in which you can specify that a column should be treated as text.
Ken
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: import txt file ot cells formated as text
I can't reproduce the problem using Chip Pearson's code:
As you see, the phone numbers are treated as text. I also tried with leading zeros, and with international access codes such as 0044 and +44.
Could you attach a sample text file that exhibits the problem?
As you see, the phone numbers are treated as text. I also tried with leading zeros, and with international access codes such as 0044 and +44.
Could you attach a sample text file that exhibits the problem?
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Panoramic Lounger
- Posts: 8176
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: import txt file ot cells formated as text
Typical! It didn't work for me honest, Gov.HansV wrote:Could you attach a sample text file that exhibits the problem?
OK, I'll see if I can anonymise one of our files as an example but not until week commencing Mon 14.
Meanwhile, thanks for looking.
Ken
-
- Panoramic Lounger
- Posts: 8176
- Joined: 25 Jan 2010, 09:09
- Location: retirement
Re: import txt file ot cells formated as text
I obviously needed a holiday as now I'm back I seem to be able to get Chip's code to work as expected and the phone numbers don't seem to be a problem any more.
Ken
Ken
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: import txt file ot cells formated as text
The phone numbers probably needed a holiday too...
Best wishes,
Hans
Hans