line spacing between sections?

[jpanther]

Hello everyone, I have a text file that I generated from Excel. This text file has 1000 lines of random information. Here is the text file from Excel:

MB416859 A 27
EV428465 U 36
JS218719 G 167
OU690872 G 126

This is what the text file needs to look like:

AB112233 A 134
EE555555 G 18
OU690872 U 122

Is there a way of formatting the cells so there is only one space between each section? I would not enjoy going through line by line and correcting it. I hope there is a way of doing it by columns. Thank you.

[quekbc]

Can you clarify or post a sample workbook (with no confidential / private info) indicating before and after?

They both look the same to me...

[FDibbins]

Hi, welcome to the forum

MB416859 A 27
EV428465 U 36
JS218719 G 167
OU690872 G 126

This is what the text file needs to look like:

AB112233 A 134
EE555555 G 18
OU690872 U 122

Hopefully, those are 2 completely different sets of data, because I can see no comparison between them?

Formatting does not change the cell contents, only it's appearance. If you have more than 1 space between your data, there are 2 ways I can think of...
1. Use Find/Replace. Find "space" "space", replace with "space
2. use a helper column with the TRIM() function. then copy/paste values back over your data, and delete the helper

[jpanther]

When I post my text file to the forum, it corrects it like the bottom example. For example, the first line looks like this:
MB416859 <8spaces> A <8spaces> 27 and the rest of the lines follow the same suit, for all 1000 lines. I'm not sure how to correct this by highlighting the whole column and moving everything over. I can do it line by line but that would be a headache.

[FDibbins]

Yes, the forum removes extra spaces when you copy here. (there are 4 spaces between these words)
Thats why you were asked for a sample workbook

Did you try the Find/Replace?

[Rick Rothstein]

Hello everyone, I have a text file that I generated from Excel. This text file has 1000 lines of random information. Here is the text file from Excel:

MB416859 A 27
EV428465 U 36
JS218719 G 167
OU690872 G 126

This is what the text file needs to look like:

AB112233 A 134
EE555555 G 18
OU690872 U 122

Is there a way of formatting the cells so there is only one space between each section? I would not enjoy going through line by line and correcting it. I hope there is a way of doing it by columns. Thank you.

You appear to want to remove spaces (and Tabs I think) between words even though this thread's title say "line spacing". Assuming that, this macro should operate directly on the text file without needing to copy any data to the worksheets first. Simply replace my example path/filenames (indicated in red) with the path/filename to your actual text file.

Please Login or Register  to view this content.

[jpanther]

I want just one space between the words when it is saved as a text file. I am able to remove all the tabs and spaces in Excel and make it all one big string, but then I'm not able to add a space between the words when I save it again as a text. Will the macro just take away the spacing ? I never worked with macros, where would I place this after I replace the file location? Thank you.

[jpanther]

Thank you for your help. I can't locate the Find/Replace. Also your instructions say: Find "space" "space", replace with "space. How does that work?

[willehahr]

Find and replace is the simplest option. I have Excel 2011 for Mac, but the directions should be similar. Don't forget to back up the file.

1. Open up your Excel file in Microsoft Excel
2. On the toolbar at the top, click "Edit" -> "Replace" (or Find and Replace)
3. Under "Find What", type in 8 spaces, nothing more. The field should appear blank, but it does have 8 spaces.
4. Under "Replace", only put one space.
5. Click "Replace All".

This should do it, but if I am wrong feel free to correct me.

[Rick Rothstein]

Will the macro just take away the spacing ? I never worked with macros, where would I place this after I replace the file location?

The macro will leave a single space between each word within each cell. I could not tell from the line before the one I quoted above whether your ultimate goal is to join each cell together so that everything is in one cell at the end or not. If that is what you want, I can change the macro to do that for you.

I never worked with macros, where would I place this after I replace the file location?

See below for instructions. One note... as written, the code will physically replace the original file. If that is not what you want, change the file name in the second red section to some name other than the original file's name.


HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MoveStatesUpOneRowInColumnF) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.