import txt file ot cells formated as text

[stuck]

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

[StuartR]

Have a great holiday Ken - will you be taking your bike from Yorkshire to London?

[stuck]

will you be taking your bike from Yorkshire to London?

No, taxi to MAN, plane to Austria.

Ken
PS any word on the VBA problem?

[HansV]

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!

[stuck]

This will start the Text Import Wizard, in which you can specify that a column should be treated 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.

Ken

[HansV]

I can't reproduce the problem using Chip Pearson's code:

S0830.png

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?

[stuck]

Could you attach a sample text file that exhibits the problem?

Typical! It didn't work for me honest, Gov.

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

[stuck]

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

[HansV]

The phone numbers probably needed a holiday too...