Importing text into Excel but by character

[cmcreynolds]

Hello, I apologize if this has been answered before, but I would appreciate redirection if it has:

I have received data that is in a text file and I need it in Excel (2013). However, as far as I can tell, each column is a character, not separated by a space or tab.

I'm not sure how to proceed to get it into a usable format.

Here are some rows:

01081 1 2422411111 1 11 1 62
0109 11 423432 111 1 11 21
0110 1 5513311 1 1 1 1 1 21
0001 22

(the record 0001 should have 27 spaces between the 1 and the leftmost 2)

I do know that the first four characters are the survey's ID and each character is a response (or lack thereof) to a question on the survey. There should be 33 columns all together.

Any suggestions?

Thank you.

 

[Ruddles]

If I understand you correctly, you need to use the Text to Columns function. Select the Fixed width option and place your delimiters after columns 4,5,6,7... etc.

 

[Tetra201]

Import it as fixed-width data:

File --> Open --> Computer --> Browse --> Text Files (*.txt) --> Open --> Step 1 of 3: Fixed width --> Step 2 of 3: Create as many break lines as you need --> Finish

 

[Smitty]

(the record 0001 should have 27 spaces between the 1 and the leftmost 2)

Is there a header row identifying which number belongs to which question? If not, how do you know what goes where?

 

[Rick Rothstein]

Is there a header row identifying which number belongs to which question? If not, how do you know what goes where?

Click on "Reply With Quotes" in Message #1 and, if the spaced out text does not appear to you, click the first icon in the comment box's ribbon to switch the comment box into WYSISWYG mode. Once you see the spaced out data, you can copy it and then select cell A1 on a worksheet and press CNTL+V to paste the data into the cells of Column A (assuming your last use of Text To Columns did not include a space delimiter). You can then use Text To Column Fixed Width (adding all the necessary vertical column lines to put each character into its own column).

 

[cmcreynolds]

Geesh -

Thank you - I was looking at "Fixed Width" as Excel fixed them and I couldn't adjust them. I hate trying to problem solve 15 minutes before the end of the day.

Again, thank you