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
989 Views

Similar Articles

[PageSpeed] 49

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:

DoModal() question.
I have a dialog class dlg. When I use dlg.DoModal(), there're always two buttons "Cancel" and "Ok" on popup window which I didn't put there. They seem to be put there by system default. How to remove them? Thanks. Frank E Rogers wrote: > I have a dialog class dlg. When I use dlg.DoModal(), there're always two > buttons "Cancel" and "Ok" on popup window which I didn't put there. They > seem to be put there by system default. How to remove them? > Thanks. > > First, you better add message handlers for them. Your ha...

InPlace ugrade from Exchange 2000 to 2003 recovery question
Hi, Will shortly be doing an inplace upgrade from Exchange 2000 enterprise edition to Exchange 2003 Enterprise edition. After we do all the steps ie. Forestprep/domainprep etc then do the actual upgrade if the upgrade fails how do we roll back. We use Brightstor Arcserve version 11 which is supported by Exchange 2000 and we backup doing the Full method not bricks level. I have documentation on how to restore Exchange using this but this just restores the database to my knowledge (never had to do it) with exchange 2000 so how do I roll back a failed upgrade. We have to do an inplace...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

Wrapping text in a cell
In a single cell, suppose I want text to appear on two lines. Viz: Case One Case Two How do I do that so that I specify the wrap point? Thanks! If you are typing the data into the cell use Alt-Enter between each string to indicate where you want a line break to occur. Case One<Alt-Enter>Case Two Alt + Enter -- Lilliabeth ------------------------------------------------------------------------ Lilliabeth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27741 View this thread: http://www.excelforum.com/showthread.php?threadid=476428 If you...

Is it possible to highlight text?
I'd like to highlight just some of the text in a cell - not the entire thing. Thanks Select the cell and in the edit bar you can then select (highlight) some of the text with the mouse and format how you wish, eg bold, different colour, different background colour, different font/font size etc. Hope this helps. Pete This will NOT work in a formula. Works only if all text. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Pete_UK" <pashurst@auditel.net> wrote in message news:1139215455.134469.285660@z14g2000cwz.googlegroups.com... > Select the cell ...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

simple xss question
Hello, One thing I dont understand about XSS: 1.There is a page with a text box 2. I inject some Javascript into that textbox that shows a form in a new div that can send content somewhere. Am I not the only one that sees this form? What good is it if the next person that goes to the website just sees the page sent from the server correctly? Thanks Its usually more of a problem when they get access to your filesystem or database ... As i've found out the hard way On 16/02/2010 14:51, in article 49e2f337-0f2c-46f1-87d4-b58b1275ef40@f17g2000prh.googlegroups.com, "...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

problem in changing the text of sentences before tables
I am developing a word automation application. In a method of mine, I change the text of some sentences of an opened word file, but the problem is when I change the text of a sentence which located before a table, it will be moved to the first cell of the table. My code is as follow: void myMethod( long startingSentenceNumber, const char *toBeSearched, const char *replacement, bool replace ) { Range currentSentenceRange; Selection sentenceSelection; Sentences sentencesList = m_document.GetSentences(); long sentencesCount = sentencesList.GetCount(); CString replacementCStr(...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

Question Re: Migration of Exchange to New Server.
I'm in the process of replacing an aging Exchange server with a brand new server. Once complete the old server will be taken off line and recycled. I figure the easiest way to do this is bring the new server up as a second Exchange server within the domain and then move the mail boxes from the old server to the new server but I have a couple of questions. 1) How do I move the public folders? I'm assuming I can setup Replication between the two servers and that will migrate the public folder data. 2) What happens when the current server goes off line? Other then having to upd...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

html or plain text in email
Using MS Outlook 2000, when I want to reply to or forward an email, how do I get that reply or forward to be in html rather than plain text. When I create a new email it defaults to html, but when I reply or forward, it defaults to plain text. Thank you. using the forward or reply/all from menu the message will take on the config of the original sender. So the sender of the this message has txt as default editor. If you want to use your default (html) you will have to create new message and copy all text out of original past in your new message. Hope this helps. >-----Original ...

Trendline Question
Hello, I have a chart from a simple data set that plots a new point each day. It's my blood p vs date. I also have it show on the chart a "Trendline" linear fit, which I guess is the rms of the data points. Works just fine. Question: Is it possible to have the Trendlinebe computed for, and show on the chart for, Only a particular range of dates in the data, rather than the for the whole data set ? e.g., if my data goes from Sept, 2006 to the present, can i just have the Trendline show for the Jan 2007 to the present data points ? How, please ? Thanks, Bob Hi, Ye...

not really a MFC question
Hi, maybe not the best thread to post but here is my question : I am trying to compile a light version of STL called ustl on win32 but I get the following errors : 1>c:\program files\microsoft visual studio 8\vc\include\ustl\cmemlink.h(61) : error C2535: 'void ustl::cmemlink::link(const void *,ustl::cmemlink::size_type)' : member function already defined or declared 1> c:\program files\microsoft visual studio 8\vc\include\ustl\cmemlink.h(60) : see declaration of 'ustl::cmemlink::link' When i look at source code I find : void link (const void* p, siz...

Caculating Columns Between Certain Dates
I have a spread sheet with for simplicity 4 columns Column 1 is a Date Column, Column 2 is a Company Name, Column 3 is a Payment amount X Column 4 is a Payment amount Y The amount due is the sum of X+Y between the relevant dates. The entries are in date order and the Company names random. The number of entries per company varies also. I want to be able to calculate the amount due for each company over a thee month period between certain varied dates. Questions 1. Is there a way to enter the dates to calculate between. 2. What is the best way to perform the calculations for each company?...

condional formating count question
Hi, I have a row in which I have a condition that if two numbers are identical its formating the 2nd number in Red Font, Is there a way or formula that I can use to count these red font Numbers? Thanks in advance for you help, It is best to use the same means in a formula that you used to make the font RED in the first place rather than trying to use a function to check the color of a cell. You can take a look at Chip Pearson's page, Functions For Working With Cell Colors http://www.cpearson.com/excel/colors.htm for counting cells in a range with involving normal cell coloring for ...