how can I "Un-delimit" my file?

M

my8950

Board Regular
Joined
May 1, 2009
Messages
162
I've got a txt file that I like to open in Excel, space delimit so it puts all my data points into seperate cells/table to do what I do with. After I'm done, I need to be able to save the data back in an undelimited format.

i.e.

Original comes out like:
255 255 255 255 255 255 255

I open in Excel and work over, but seperate to cells and it looks like this when I copy paste back into notepad.

255 - 10 spaces or so - 255 - 10 spaces or so - 255 - 10 spaces or so - 255 - 10 spaces or so - 255.....

And so on. I've tried to just replace all and count the numbers of spaces, but its not working out so well.
I feel like I should be able to do this within excel as well since I can change it from the original format.

Anyone?

Thanks!
 
Last edited:

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
one method

save as comma seperated *.csv

open in word
and use

find ","
don't use quotes
replace with nothing

a second way is
=a1&b1&c1&d1&e1&f1&g1
go out as far as you need.

copy and paste down
 
Upvote 0
I always ask the same question in these cases, but ... are you sure you need to worry about this? Many programs opening this file will either ignore the extra spaces, or handle them appropriately anyway. Same with tabs, commas, tabs, and so on...

Is it an absolute requirement to have a single space as delimiter? I might use a tab-delimited file or comma-delimited file if it works otherwise.
 
Upvote 0
I am not sure right now if its necessary, but trying to switch the data back and forth, when I modify some things in Excel and then save it as a text, trying to recompile it and the compiler crashes...I'm trying to eliminate as many variables as possible.
 
Upvote 0
Actually.. I think you'll need something like this instead

Code: 
Sub Export_File()
    saveDir = "C:\DAL\JUNK\"
    targetFile = saveDir & "TestExport.txt"
    If Dir(targetFile) <> "" Then Kill targetFile
    Open targetFile For Output As #1
    For trow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        lastcol = Cells.Find(What:="*", After:=[A1], _
        SearchOrder:=xlByColumns, _
        SearchDirection:=xlPrevious).Column
        For tcol = 1 To lastcol
            If Cells(trow, tcol) = "" Then Exit For
            Mystring = Mystring & Cells(trow, tcol) & " "
        Next 'tcol
        Print #1, Mystring
        Mystring = ""
    Next 'trow
    Close #1
End Sub

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:right; ">255</td><td style="text-align:right; ">255</td><td style="text-align:right; ">255</td><td style="text-align:right; ">255</td><td style="text-align:right; ">255</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">355</td><td style="text-align:right; ">355</td><td style="text-align:right; ">355</td><td style="text-align:right; ">355</td><td style="text-align:right; ">22</td><td style="text-align:right; ">23</td><td style="text-align:right; ">27</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">12</td><td style="text-align:right; ">89</td><td style="text-align:right; ">98</td><td style="text-align:right; ">22</td><td style="text-align:right; ">8777654</td><td style="text-align:right; ">1</td><td >JJJ</td></tr></table>

2uh76oh.jpg
 
Upvote 0
actually a very important question:

what is the format of the input data initially

can you give an example

you may be able to goto text to data with space delimited, but it may be fixed space or multiple spaces between

in other words what format does it need to be when you are done with it?

do some of the values need to be spearated by several spaces/
 
Upvote 0
The original extension is .log
It is a text file, and needs to be the same when I save it and try to re-compile it.
I'm not sure if there is a way to add an attachment onto this post, but I can upload a file if it would help....
Or try cut & paste....
The top line that says do not edit this line is untouched, I copy the rest and modify accordingly, then re-paste into the file, so I'm not sure how this will come out when pasted here and if you then paste it and edit, save, etc...

3 1 155 1 0 Do not change this line!
102 102 102 115 115 115 115 115 115 115 115 115 115 115 115 115
102 102 102 115 115 115 115 115 115 115 115 115 115 115 115 115
102 102 102 115 115 115 115 115 115 115 115 115 115 115 115 115
89 89 89 102 102 102 102 102 102 102 102 102 102 102 102 102
79 79 79 92 92 92 92 92 92 92 92 92 92 92 92 92
70 70 70 76 76 76 76 76 76 76 76 76 76 76 76 76
71 71 71 71 71 71 71 71 71 71 71 71 71 71 71 71
65 65 65 65 65 65 65 65 65 65 65 65 65 65 65 65
60 60 60 60 60 60 60 60 60 60 60 60 60 60 60 60
59 59 59 59 59 59 59 59 59 59 59 59 59 59 59 59
58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58
57 57 57 57 57 57 57 57 57 57 57 57 57 57 57 57
53 53 53 53 53 53 53 53 53 53 53 53 53 53 53 53
51 51 51 51 51 51 51 51 51 51 51 51 51 51 51 51
50 50 50 50 50 50 50 50 50 50 50 50 50 50 50 50
48 48 48 48 48 48 48 48 48 48 48 48 48 48 48 48
 
Upvote 0
The example looks ok, comparing to my original, so if it's cut & pasted I think it would be ok. Thats the raw file, and it needs to be the same as that when I re-save it. I edit the numbers in the text as needed, they are anywhere from 0-255, which is acceptable, so thats not the problem.

Phx, thanks for that code, I'll give it a shot!
 
Upvote 0
Using your supplied sample data

<b>TEST EXPORT</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:37px;" /><col style="width:33px;" /><col style="width:34px;" /><col style="width:44px;" /><col style="width:35px;" /><col style="width:35px;" /><col style="width:43px;" /><col style="width:63px;" /><col style="width:37px;" /><col style="width:35px;" /><col style="width:26px;" /><col style="width:40px;" /><col style="width:33px;" /><col style="width:39px;" /><col style="width:31px;" /><col style="width:34px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-family:Verdana; text-align:center; ">3</td><td style="text-align:center; ">1</td><td style="text-align:center; ">155</td><td style="text-align:center; ">1</td><td style="text-align:center; ">0</td><td style="text-align:center; ">Do</td><td style="text-align:center; ">not</td><td style="text-align:center; ">change</td><td style="text-align:center; ">this</td><td style="text-align:center; ">line!</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-family:Verdana; text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-family:Verdana; text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-family:Verdana; text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td><td style="text-align:center; ">115</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-family:Verdana; text-align:center; ">89</td><td style="text-align:center; ">89</td><td style="text-align:center; ">89</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td><td style="text-align:center; ">102</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-family:Verdana; text-align:center; ">79</td><td style="text-align:center; ">79</td><td style="text-align:center; ">79</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td><td style="text-align:center; ">92</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-family:Verdana; text-align:center; ">70</td><td style="text-align:center; ">70</td><td style="text-align:center; ">70</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td><td style="text-align:center; ">76</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-family:Verdana; text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td><td style="text-align:center; ">71</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-family:Verdana; text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td><td style="text-align:center; ">65</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-family:Verdana; text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td><td style="text-align:center; ">60</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-family:Verdana; text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td><td style="text-align:center; ">59</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-family:Verdana; text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td><td style="text-align:center; ">58</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-family:Verdana; text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td><td style="text-align:center; ">57</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-family:Verdana; text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td><td style="text-align:center; ">53</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="font-family:Verdana; text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td><td style="text-align:center; ">51</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="font-family:Verdana; text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td><td style="text-align:center; ">50</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="font-family:Verdana; text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td><td style="text-align:center; ">48</td></tr></table>

j5a1bk.jpg
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
If and ElseIf confusion !
Replies
5
Views
287
Micron
M
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
107
Kaper
Kaper
P
  • Question Question
Exiting a Textbox Problem
2
Replies
10
Views
695
Pacman52
P
G
  • Question Question
Making transparent solid 3D model
Replies
0
Views
200
gifariz
G
P
  • Solved
Advice needed on where to put code that stops duplicate entries
Replies
5
Views
597
Pacman52
P

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,921
Latest member
BBQKING

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top