Importing from Text File

[cubs610]

Hi Everyone,


Trying to import a text file in Excel. The file is one long column of addresses separated for the most part, by the hard returns. The import wizard did not cooperate very well; even when I tried to use the ALT+0020(or ALT+0010). No luck. OFFSET formula is a pain to deal with-- looking for easier solution.


Goal is to import the list of vertical addresses, with end result one record per row not column), when the delimiter is the 'Enter' key.


Any thoughts?

 

[Luke M]

Two step process, but this might work for you. Find and replace all the hard returns with something unique, like "$" (since it's an address, assuming this does not naturally appear). Then do Text-to-columns using the $ as the delimiter.

PS If having trouble finding the carriage returns, a common trick is to determine the exact character code (use the CODE function) if it's not the default CHAR(10) line return, and you can then run this brief macro.

[pre]

Sub ChangeReturns()
Range("A:A").Replace Chr(10), "$"
End Sub

[/pre]

 

[SirJB7]

Hi, cubs610!


If it's just one column with chr$(13)+chr$(10) (carriage return & line feed) you should be able to simple opening using both options "delimited" or "fixed with" from Data tab, Get/Import External Data group, From Text icon...

Otherwise consider uploading a sample file.


Regards!

 

[cubs610]

Beatiful....


thanks

 

[SirJB7]

Hi, cubs610!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!