Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-22-2014, 12:11 AM
Bambi555 Bambi555 is offline Windows 7 32bit Office 2007
Novice
 
Join Date: Sep 2014
Posts: 1
Bambi555 is on a distinguished road
Red face Exporting from excel into a specific format

Hi all,



I'm new here, so sorry if I've posted in the wrong place, and I know nothing about computers so sorry in advance if this is a stupid question. I'm trying to export data from excel in a specific format. I've attached an example of what the excel file looks like. The format I want it to be in is in the first text file (samp_inp_2b.txt). When I export from excel as unicode or tab delimited it results in the second text file (sample2db.txt). As far as I can tell the two text files are the same but the line breaks are different, in the first one the linebreaks are there but don't display for some reason (at least in notepad). Does anyone know how to export data from excel to get the first text file format? If anyone knows how to directly convert between the 2nd and 1st text files that would also do the job.
Thanks in advance!
Attached Files
File Type: xlsx sample2e.xlsx (31.0 KB, 9 views)
File Type: txt samp_inp_2b.txt (10.0 KB, 9 views)
File Type: txt sample2db.txt (10.8 KB, 6 views)
Reply With Quote
  #2  
Old 09-22-2014, 03:18 AM
thetraininglady thetraininglady is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Sep 2014
Location: Sydney, Australia
Posts: 203
thetraininglady is on a distinguished road
Default

Could you open the exported version in MS Word and use the Replace function to search for the tab marker and replace it with nothing which will remove it from the file and then once they are all gone save as TXT again?
__________________
Belinda ~ Author@ TheTrainingLady.com
Tips & Tricks for using Microsoft Office & Windows.
Reply With Quote
  #3  
Old 09-22-2014, 08:08 AM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 16,300
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Excel does not have an export format that would remove the line breaks from the data. You could, however, produce such an output using a Directory mailmerge in Word. To do this:
1. Start Word and go to Mailings
2. Choose Start Mailmerge>Directory
3. Choose Select Recipients>Use existing List, then navigate to and select your workbook.
4. Choose Insert Mergefield and click on each of the items, in turn
5. Press Alt-F9 to reveal the mergefield coding, it should look like:
{ MERGEFIELD "_WGOP_samp_inpu_2" }{ MERGEFIELD "F2" }{ MERGEFIELD "F3" }{ MERGEFIELD "F4" }{ MERGEFIELD "F5" }{ MERGEFIELD "F6" }
6. Edit each of these fields by inserting \f "→", where the → is a TAB character, so they all end up as:
{ MERGEFIELD "_WGOP_samp_inpu_2" \f "→"}{ MERGEFIELD "F2" \f "→"}{ MERGEFIELD "F3" \f "→"}{ MERGEFIELD "F4" \f "→"}{ MERGEFIELD "F5" \f "→"}{ MERGEFIELD "F6" \f "→"}
7. Choose Finish & Merge>Edit Individual Documents>OK
8. The output can now be saved as a text file from Word.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Inputting a number into word in a specific format Jo 4x4 Word 7 07-19-2014 12:45 AM
Importing/Exporting data using Excel and some specific rules stagg201 Excel 1 03-27-2014 08:58 PM
Exporting workbook specific sheet columns on exit shabbaranks Excel Programming 4 08-21-2012 08:00 AM
Applying specific format to doc jrodriguez Word 3 07-23-2012 08:38 PM
Specific format for an academic paper in finance Frednd Word 1 02-25-2011 04:55 PM


All times are GMT -7. The time now is 06:54 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft