Special Text to Column question

I have a special problem that I hope the gurus here can solve.

Once a month or so, I receive a text file that includes about 400
lines of 323 characters each.  Groups of characters have different
meaning depending on their position in the string.  Some are grouped
in as little as one character, other groups are as long as 25
characters.  There are no delimiting characters like commas, spaces or
the like

I usually open the file in Excel and click Text to Columns and select
Fixed Width.  From there I scroll left to right and click on the
necessary widths for the groups involved "9, 5, 2, 2, 1, 25, etc"

My question is this:  Is there a way to run the text to column
function using existing dilimitation.  I've tried pasting into the
existing sheet, but can't figure out a way to bypass the above
operation.

I am using Excel 2010.

Thanks in advance for any assistance.
0
notgonna (1)
5/27/2011 11:20:14 PM
excel 39879 articles. 2 followers. Follow

5 Replies
598 Views

Similar Articles

[PageSpeed] 0

notgonna@tell.ya wrote on 5/27/2011 :
> I have a special problem that I hope the gurus here can solve.
>
> Once a month or so, I receive a text file that includes about 400
> lines of 323 characters each.  Groups of characters have different
> meaning depending on their position in the string.  Some are grouped
> in as little as one character, other groups are as long as 25
> characters.  There are no delimiting characters like commas, spaces or
> the like
>
> I usually open the file in Excel and click Text to Columns and select
> Fixed Width.  From there I scroll left to right and click on the
> necessary widths for the groups involved "9, 5, 2, 2, 1, 25, etc"
>
> My question is this:  Is there a way to run the text to column
> function using existing dilimitation.  I've tried pasting into the
> existing sheet, but can't figure out a way to bypass the above
> operation.
>
> I am using Excel 2010.
>
> Thanks in advance for any assistance.

Check out the Mid$() function. It will allow yo to specify starting 
position and length (#characters).

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
5/28/2011 1:28:42 AM
On Fri, 27 May 2011 16:20:14 -0700, notgonna@tell.ya wrote:

>I have a special problem that I hope the gurus here can solve.
>
>Once a month or so, I receive a text file that includes about 400
>lines of 323 characters each.  Groups of characters have different
>meaning depending on their position in the string.  Some are grouped
>in as little as one character, other groups are as long as 25
>characters.  There are no delimiting characters like commas, spaces or
>the like
>
>I usually open the file in Excel and click Text to Columns and select
>Fixed Width.  From there I scroll left to right and click on the
>necessary widths for the groups involved "9, 5, 2, 2, 1, 25, etc"
>
>My question is this:  Is there a way to run the text to column
>function using existing dilimitation.  I've tried pasting into the
>existing sheet, but can't figure out a way to bypass the above
>operation.
>
>I am using Excel 2010.
>
>Thanks in advance for any assistance.

You might be able to use a macro.  It is hard to recommend one completely as I don't know enough about your data, but if you place a macro like below into a regular module, and import the data into column A; the macro will parse the column into your predetermined column widths, treating each column as different types of data (if necessary), just like the Text To Columns wizard does.

As written, it preserves column A and starts the parsing in column B; but once you get it debugged, you could overwrite column A.  Note that you will have to set up the FieldInfo parameter -- I just used what I did for testing.

You might record a macro while you are doing the Text To Columns manually.  That should also give you something to work from.

This should, at least, get you started.  Note that there are many possible ways to set up the range to parse; as written, it selects everything in Column "A".

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.

===========================
Option Explicit
Sub TTCSpecial()
  Dim rg As Range
Set rg = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))

rg.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
    fieldinfo:=Array(Array(0, xlGeneralFormat), Array(9, xlTextFormat), _
        Array(15, xlTextFormat), Array(18, xlTextFormat), Array(20, xlTextFormat))
        
End Sub
=============================
0
ron6368 (329)
5/28/2011 3:04:34 AM
Ron Rosenfeld brought next idea :
> On Fri, 27 May 2011 16:20:14 -0700, notgonna@tell.ya wrote:
>
>> I have a special problem that I hope the gurus here can solve.
>> 
>> Once a month or so, I receive a text file that includes about 400
>> lines of 323 characters each.  Groups of characters have different
>> meaning depending on their position in the string.  Some are grouped
>> in as little as one character, other groups are as long as 25
>> characters.  There are no delimiting characters like commas, spaces or
>> the like
>> 
>> I usually open the file in Excel and click Text to Columns and select
>> Fixed Width.  From there I scroll left to right and click on the
>> necessary widths for the groups involved "9, 5, 2, 2, 1, 25, etc"
>> 
>> My question is this:  Is there a way to run the text to column
>> function using existing dilimitation.  I've tried pasting into the
>> existing sheet, but can't figure out a way to bypass the above
>> operation.
>> 
>> I am using Excel 2010.
>> 
>> Thanks in advance for any assistance.
>
> You might be able to use a macro.  It is hard to recommend one completely as 
> I don't know enough about your data, but if you place a macro like below into 
> a regular module, and import the data into column A; the macro will parse the 
> column into your predetermined column widths, treating each column as 
> different types of data (if necessary), just like the Text To Columns wizard 
> does.
>
> As written, it preserves column A and starts the parsing in column B; but 
> once you get it debugged, you could overwrite column A.  Note that you will 
> have to set up the FieldInfo parameter -- I just used what I did for testing.
>
> You might record a macro while you are doing the Text To Columns manually.  
> That should also give you something to work from.
>
> This should, at least, get you started.  Note that there are many possible 
> ways to set up the range to parse; as written, it selects everything in 
> Column "A".
>
> To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
> Ensure your project is highlighted in the Project Explorer window.
> Then, from the top menu, select Insert/Module and
> paste the code below into the window that opens.
>
> To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the 
> macro by name, and <RUN>.
>
> ===========================
> Option Explicit
> Sub TTCSpecial()
>   Dim rg As Range
> Set rg = Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
>
> rg.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
>     fieldinfo:=Array(Array(0, xlGeneralFormat), Array(9, xlTextFormat), _
>         Array(15, xlTextFormat), Array(18, xlTextFormat), Array(20, 
> xlTextFormat))
>         
> End Sub
> =============================

Very nice, Ron! Didn't think to use TextToColumns because I've never 
used it. Thanks for posting this!

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
5/28/2011 3:32:17 AM
On Fri, 27 May 2011 23:32:17 -0400, GS <gs@somewhere.net> wrote:

>Very nice, Ron! Didn't think to use TextToColumns because I've never 
>used it. Thanks for posting this!
>
>-- 
>Garry

I think the key point is to do the splitting within a macro that is stored either in the workbook itself or, if a different workbook is generated each month, possibly in an add-in or personal.xls file.

The Text-to-columns method should be faster than looping through the range and populating each target cell using the Mid function.  
0
ron6368 (329)
5/28/2011 10:38:21 AM
Ron Rosenfeld has brought this to us :
> On Fri, 27 May 2011 23:32:17 -0400, GS <gs@somewhere.net> wrote:
>
>> Very nice, Ron! Didn't think to use TextToColumns because I've never 
>> used it. Thanks for posting this!
>> 
>> -- 
>> Garry
>
> I think the key point is to do the splitting within a macro that is stored 
> either in the workbook itself or, if a different workbook is generated each 
> month, possibly in an add-in or personal.xls file.
>
> The Text-to-columns method should be faster than looping through the range 
> and populating each target cell using the Mid function.  

I agree!

My suggestion for using Mid$() was based entirely on my lack of knowing 
about TextToColumns. I prefer to use built-in functions over VBA 
whenever possible because I do believe that the built-in functions are 
faster and more efficient than a VBA dupe (generally speaking).

Thanks for the feedback...

-- 
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc


0
gs3102 (378)
5/28/2011 2:28:32 PM
Reply:

Similar Artilces:

Can anyone answer this Rules question????
I have created a rule for any rule with [Norton Antispam] be marked as read but doesn't get marked read. If I manualy run the rule but on it's own it doesn't? I think the problem is unless the ENTIRE suject matches, the rule won't apply. Is there a wildcard or away to get any message with [Norton Antispam] to be marked as read? A wildcard to ignore [Norton Antispam] and read another part of the subject? Rick wrote: > I have created a rule for any rule with [Norton Antispam] be marked > as read but doesn't get marked read. If I manualy run the rule but &...

Assign number value to a field with text in it?
Is this possible to do? I want a cell to have a number assigned to it, but display text in the cell, so I can use the cell in a mathmatical formula. I'm using Excel 2003 to track how many seats I have left in each conference room. I want the name of a room to equal a number. For example: I want the text "Executive Room A" to equal 105 Does anyone know a way to do this with out putting them in different fields. -Sharon --- Message posted from http://www.ExcelForum.com/ You may want to use Help to learn about VLOOKUP. You can make a table on say Sheet2 with the room names a...

Count text cells if condition in row is true, over many rows.
Using Excel 2003 - 84 columns, up to 250 rows. By groups of 3 adjacent columns, I want to put (in merged cell at top of each group of 3) a formula showing total of cells with text, IF a separate cell in column CM in each row has a particular value. So, there will be 28 totals (84/3) in top row. If CM2="E", CM3="S", CM4="E", CM5="E", CM6="S", etc, and using condition "E", the formula in merged D1 will count all text cells in columns D,E,F of rows 2,4,5,etc. The formula in merged G1 will count all text cells in columns...

Special Characters
Is there a quick way to convert all special characters to XML format for example '&' to &amp; or '+' sign to ? ? MAF wrote: > Is there a quick way to convert all special characters to XML format for > example '&' to &amp; or '+' sign to ? ? '&' needs to be escaped as &amp; but the '+' sign does not need to be escaped in XML. How you do it depends on the .NET APIs you use, if you use an XmlTextWriter to create XML then the WriteString method for instance does the escaping for you. If you use the DOM th...

comparing two columns? #2
I have two columns of phone numbers, one is column C and one is column D. I would like to take the numbers in column D and match them to the numbers in column C, removing the numbers that dont match. Can anyone help? Hi Steve Chip Pearson has a great page on working with duplicates ... http://www.cpearson.com/excel/duplicat.htm have a look down the bottom of the page for comparing lists -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "steve g via OfficeKB.com" <forum@nospam.OfficeKB.com> wrote in message news:7fe142636f88...

justifying text
How do I remove the extra spaces on a line when I justify text? I want to full justify and ctrl+J does not do it. Thanks Abbylady What extra spaces? Do you mean the extra space between letters and words? If so... then are you sure you really want justified text? Extra space between letters and word are how Word accomplishes justification. With proportionally spaced characters and uneven amounts of text on each line, justification requires it. There are different justification methods that can be selected using Word options... Perhaps one of them will give you something c...

no question s answered
i notice no question s ever get answered onin this group "Peter" <peter.codner@lineone.net> wrote in message news:#4ORxbUqDHA.3612@TK2MSFTNGP11.phx.gbl... > i notice no question s ever get answered onin this group > > Well that's odd because I've answered several myself today....... "rifleman" <me9@privacy.net> wrote in message news:botsri$1hkrhr$1@ID-108938.news.uni-berlin.de... > > "Peter" <peter.codner@lineone.net> wrote in message > news:#4ORxbUqDHA.3612@TK2MSFTNGP11.phx.gbl... > > i notice no quest...

Text is not showing up in cell. Why not?
I type in text and it shows up in the "fx" box above and I can see text in white when I hightlight a range of cells that contains this cell, but the text doesn't show in the cell normally. The cell is formatted "General" like a lot of other cells around it are that show text. Make sure that the cell isn't formatted as white font on a white fill. TomBrooklyn wrote: > > I type in text and it shows up in the "fx" box above and I can see text in > white when I hightlight a range of cells that contains this cell, but the > text doesn'...

update text box in current form
Hi, On a continuous form I've a textbox where users should put a link to a file. The users can type the path and filename but I want to make it more user-friendly by adding a button (which opens a file dialog) next to the textbox. But when I put the value in the textbox all the textboxes on the form have this value. How to give only the current record this value? Did you bind the textbox to a field? -- KARL DEWEY Build a little - Test a little "HDI" wrote: > Hi, > > On a continuous form I've a textbox where users should put a link to a > file. > >...

Length of Text
I have a text field with 4,5 or 6 character text content. In a query, how can I extract all records with a length of 4 and then add 6 leading zeros to it Thanks For a select or update query? Add an expression field to the query which has len(trim([MyField])) as the expression, and 4 as the criteria. Then add another expression field, with the expression as "000000" & [MyField] If you are using an update query, the "000000" & [MyField] will go in the Update To row for [MyField]. HTH John TeeSee wrote: >I have a text field with 4,5 or 6 character text con...

If text exists pull text from another cell....how to do it?
I have a schedule with multiple sheets that I have made for my staff. What I am having trouble with is getting text from one sheet to another. I have Servers, Host, BarBus sheets as well as Mon, Tue, Wed, Thur, Fri, Sat, and Sun sheets. In the Servers sheet in A1 Column I have their names B1:O1 are their shifts. The setup is the same for Host and BArBus sheets. B1 has three shifts AM, OCA, XPOA B2 has four shifts PM, BR, OCP, XPOP What I can not figure out is how to pull the names of the people that are working. Example: On Sheet Mon I have Servers______Host______Bar_______Bus I need...

counting Multiple answers in 1 cell + column
how do u count multiple values in the one cell and down the entire column? i want to count all magazines answers in column A1. i would like to get the answer (4) for magazines, (4) for tv, and (3) for radio. and it does not matter if the counting of the values, magazine,tv,radio, is answered in seperate cells. i tried the formula = COUNTIF(range, "value") but the formula doesnt not count the values if there are more than one value in the cell... see example below A1 A2 1 magazines, tv, radio 2 magazines 3 t...

Sending OCR text from MODI to Word
I recently scanned several documents using MODI and used the OCR function to recognize the text. When I then try to send the text to Word (2007), a new Word document opens up but no text is placed within it. I eventually get an error message that says "This action cannot be completed because the "Document 1--Microsoft Word" program is not responding. Choose 'Switch to' and correct the problem." Choosing "Switch to" give me a second error message: "An error was encountered while trying to send text to Word. Please make sure Word is proper...

undelete-abld text boxes
Hi I'm working on a 36 page document in Publisher 2003 and dragged a text box from the document onto the canvas(? - the bit at the side ) so that I could decide which page it should go into. When I've done this before the text box has "appeared" at the side of all pages until it was finally given a home when it has diappeared. I now have one with a life of its own! I lodged it in a page - and in desperation have deleted it form the document - but that hasn't made a difference. Basically the box on the canvas has stayed and causes the application to hang until it ...

Excel columns
Can you create a tab within cells? I have a chart that I need to manually tab either 4 or 6 times within the cells in order for the text to line up on my printout. I don't want the printing to start right at the edge of the chart lines. Is there an easier way to accomplish this? Excel isn't much of a word processor. If you're indenting the value in the cell, take a look at: Format|cells|Alignment Tab|Indent box. If it's between words in cell, I've always used the spacebar. And if I really, really want it to line up nicely, I use a "Courier New" font. It...

Can't Copy and Paste or Paste Special between Excel Workbooks
We have a number of Excel users in our office who cannot copy and paste between Excel workbooks. They can copy and paste between worksheets. When you highlight the section to copy and then go to the new workbook both the paste and paste special are "grayed out". This is true whether you right-click the mouse, go to the edit menu, or use control keys. This occurs with any data type and the most simple workbooks. I have seen some suggestions here but none have worked for this particular problem. I have reset the menus and renamed the .xlb files and neither helps. You can open t...

Exporting table data to an 80 column text file
I need to take a table (every row) and export it to a text file in an 80 col. format. No delimiters or quotes ("") for text. All the fields in the table add up to 80 characters. I want to make it as automatic as possible. NewHeartMan wrote: > I need to take a table (every row) and export it to a text file in an 80 col. > format. No delimiters or quotes ("") for text. All the fields in the table > add up to 80 characters. I want to make it as automatic as possible. After you select File/Export, use TXT as the file extension, select Fixed Width, click th...

Newbie Question : Making Sections Visible
Hi, I saw a presentation recently where the speaker presented the basic sections of his talk: 1) Intro 2) Problem ,... ect He had the presentation set up in such a way that as he stepped through the presentation slide by slide, on each slide off to the left the list of topics was present and the one he was currently on was highlighted. It looked really cool and it also gives the audience a sense of "where they're at" in the talk, so I'd like to incorporate it into my next presentation. How do you do this? TIA, Matt Maybe (there are several ways to do th...

"custom Views", column widths not changing
although Help says that custom views will change cell widths, it does not. Any fix for this? If not, can I autosize on the basis of part of a column only (ie, ignoring what is above or below the column range I choose? DesM -- DesM ------------------------------------------------------------------------ DesM's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24121 View this thread: http://www.excelforum.com/showthread.php?threadid=393811 ...

display of text and lines from CAD
In Visio 2007, I opened a .DWG,converted it, and saved is as Visio (.VSD). Some of the entities (both lines and text) don't display on the screen in the formatted color. The current theme is "none". I tried two other themes, with no apparant difference. Any idea why these would display (and print) as a different color than they are formatted? When I change the font size of the text, it does change size. When I change color, it doesn't change. help? Regards Jay ...

Dynamic chart and shrinking text boxes
Good afternoon all, I have a dynamic chart, that records the amount of forms processed eac week. Every Friday, I coallate the statistics for the past week, an input them in, and the chart then grows by one week. Perfectly norma stuff, documented regularly. What I need to know, is, if it is possible to link outside objects t parts of the chart. Example. During 02/01/04 and 23/01/04 we were down 1 FTE (full tim equivalent), and I have a text box between those dates indicating this The problem is, when I add on a new week (for example, Week Endin 06/02/04) all the dates shift backwards, and I ...

how do i get the words to stay in its own column?
I am a new excel user and i have to have a speadsheet for work tomorrow done. My problem is that I need to make a chart that contains a heading at the top, 4 columns underneth , and about 6 rows and i need for the informations that i put into the columns to stay in that column with out carring over to the next column. Can someone please help. Select the cell that you've typed in, then click Format on the menu bar when the menu opens, click Cells, when the dialog box opens, click the Alignment Tab and the click the Wrap Text box to place a check mark in it. "ldennis" wr...

TX Text Control
Does anyone know what the TX Text Control ActiveX is? Where can I get some documentation on it?\ Thanks Larry A quick google search returns http://www.textcontrol.com/tx/docs/ -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "Larry J. Siddens" <lsiddens@cstoneindy_NOSPAM.com> wrote in message news:05fa01c39cdb$40b4fdc0$a101280a@phx.gbl... > Does anyone know what the TX Text Control ActiveX is? > Where can I get some documentation on it?\ > > Thanks > Larry > ...

Text entry query
I am entering 5 lines of text into a block of cells and have Merge Cells selected in Format | Cells. When the last line exceeds a particular point in the text all that shows when I try and enter it is a single long line of #s. That line is not the longest line. Help anyone? Brian Tozer Try formatting the cell as General (not Text). If you need more than 1000 characters to show up in the cell, add a few alt-enters to force a new line--you didn't ask--but it's a common question with cells containing a lot of text. Brian Tozer wrote: > > I am entering 5 lines of text into a...

text attachments duplicat message body with all new emails
All of a sudden (since yesterday) every email I receive has a .txt attachment which contains a duplicate copy of the text in the main body of the email. I haven't changed any settings. Any ideas why or, more importantly, how to stop it? TIA ...