Text to columns question

I have some data in the following format:

Number firstname lastname schoolname

The first three fields are one word each. Schoolname can be more than
one word. I'm trying to parse the data using the data -> Text to
Columns wizard, but I cannot figure out how to tell the wizard that
there are only 4 space delimited columns in my data and not 5 for
those instances where the schoolname is two words.

Any help is appreciated. Thanks.
0
MPBrede (29)
1/22/2008 3:50:57 PM
excel 39879 articles. 2 followers. Follow

4 Replies
731 Views

Similar Articles

[PageSpeed] 19

On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote:
> I have some data in the following format:
>
> Number firstname lastname schoolname
>
> The first three fields are one word each. Schoolname can be more than
> one word. I'm trying to parse the data using the data -> Text to
> Columns wizard, but I cannot figure out how to tell the wizard that
> there are only 4 space delimited columns in my data and not 5 for
> those instances where the schoolname is two words.
>
> Any help is appreciated. Thanks.

Actually, I got this fixed. Not sure if it was the best way to do it,
but I found a way to workaround.

I split the data into the 5 columns. Then I created a new column that
concatenated the two columns that contained the school name into one
column. Then (using Paste Special) I created a new colum containing
the values from the concatenated column and I deleted the unnecessary
columns.

Now I have another question

If I now use subtatals, is there a way to sort the result of the
subtotals so that the school with the most students in it appears
first? In other words, can I sort on the subtotals? I tried and it
sorts the underlying data.

Thanks
0
MPBrede (29)
1/22/2008 4:06:00 PM
Try this on a copy of your data:

Immediately to the right of your data enter the formula:

=COUNTIF($C$2:$C$14,C2)

Where Column C is the School name, adjust the range to suit your data and 
copy down.

Next highlight all your data including the above formula and sort in 
decending order.

-- 
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"MikeB" <MPBrede@gmail.com> wrote in message 
news:cc0eae53-dd15-48e6-9f5d-513faef7c8d2@i29g2000prf.googlegroups.com...
> On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote:
>> I have some data in the following format:
>>
>> Number firstname lastname schoolname
>>
>> The first three fields are one word each. Schoolname can be more than
>> one word. I'm trying to parse the data using the data -> Text to
>> Columns wizard, but I cannot figure out how to tell the wizard that
>> there are only 4 space delimited columns in my data and not 5 for
>> those instances where the schoolname is two words.
>>
>> Any help is appreciated. Thanks.
>
> Actually, I got this fixed. Not sure if it was the best way to do it,
> but I found a way to workaround.
>
> I split the data into the 5 columns. Then I created a new column that
> concatenated the two columns that contained the school name into one
> column. Then (using Paste Special) I created a new colum containing
> the values from the concatenated column and I deleted the unnecessary
> columns.
>
> Now I have another question
>
> If I now use subtatals, is there a way to sort the result of the
> subtotals so that the school with the most students in it appears
> first? In other words, can I sort on the subtotals? I tried and it
> sorts the underlying data.
>
> Thanks
> 


0
sandymann2 (1054)
1/22/2008 5:50:02 PM
On Jan 22, 11:50 am, "Sandy Mann" <sandyma...@mailinator.com> wrote:
> Try this on a copy of your data:
>
> Immediately to the right of your data enter the formula:
>
> =COUNTIF($C$2:$C$14,C2)
>
> Where Column C is the School name, adjust the range to suit your data and
> copy down.
>
> Next highlight all your data including the above formula and sort in
> decending order.
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
> and the crowning place of kings
>
> sandyma...@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
> "MikeB" <MPBr...@gmail.com> wrote in message
>
> news:cc0eae53-dd15-48e6-9f5d-513faef7c8d2@i29g2000prf.googlegroups.com...
>
> > On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote:
> >> I have some data in the following format:
>
> >> Number firstname lastname schoolname
>
> >> The first three fields are one word each. Schoolname can be more than
> >> one word. I'm trying to parse the data using the data -> Text to
> >> Columns wizard, but I cannot figure out how to tell the wizard that
> >> there are only 4 space delimited columns in my data and not 5 for
> >> those instances where the schoolname is two words.
>
> >> Any help is appreciated. Thanks.
>
> > Actually, I got this fixed. Not sure if it was the best way to do it,
> > but I found a way to workaround.
>
> > I split the data into the 5 columns. Then I created a new column that
> > concatenated the two columns that contained the school name into one
> > column. Then (using Paste Special) I created a new colum containing
> > the values from the concatenated column and I deleted the unnecessary
> > columns.
>
> > Now I have another question
>
> > If I now use subtatals, is there a way to sort the result of the
> > subtotals so that the school with the most students in it appears
> > first? In other words, can I sort on the subtotals? I tried and it
> > sorts the underlying data.
>
> > Thanks

Nice! Thanks.
0
MPBrede (29)
1/22/2008 7:36:35 PM
I'm glad that it worked for you.

-- 
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"MikeB" <MPBrede@gmail.com> wrote in message 
news:e08c50fc-ce17-4cba-a33e-f0b90b6334f4@j78g2000hsd.googlegroups.com...
> On Jan 22, 11:50 am, "Sandy Mann" <sandyma...@mailinator.com> wrote:
>> Try this on a copy of your data:
>>
>> Immediately to the right of your data enter the formula:
>>
>> =COUNTIF($C$2:$C$14,C2)
>>
>> Where Column C is the School name, adjust the range to suit your data and
>> copy down.
>>
>> Next highlight all your data including the above formula and sort in
>> decending order.
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>> and the crowning place of kings
>>
>> sandyma...@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>> "MikeB" <MPBr...@gmail.com> wrote in message
>>
>> news:cc0eae53-dd15-48e6-9f5d-513faef7c8d2@i29g2000prf.googlegroups.com...
>>
>> > On Jan 22, 9:50 am, MikeB <MPBr...@gmail.com> wrote:
>> >> I have some data in the following format:
>>
>> >> Number firstname lastname schoolname
>>
>> >> The first three fields are one word each. Schoolname can be more than
>> >> one word. I'm trying to parse the data using the data -> Text to
>> >> Columns wizard, but I cannot figure out how to tell the wizard that
>> >> there are only 4 space delimited columns in my data and not 5 for
>> >> those instances where the schoolname is two words.
>>
>> >> Any help is appreciated. Thanks.
>>
>> > Actually, I got this fixed. Not sure if it was the best way to do it,
>> > but I found a way to workaround.
>>
>> > I split the data into the 5 columns. Then I created a new column that
>> > concatenated the two columns that contained the school name into one
>> > column. Then (using Paste Special) I created a new colum containing
>> > the values from the concatenated column and I deleted the unnecessary
>> > columns.
>>
>> > Now I have another question
>>
>> > If I now use subtatals, is there a way to sort the result of the
>> > subtotals so that the school with the most students in it appears
>> > first? In other words, can I sort on the subtotals? I tried and it
>> > sorts the underlying data.
>>
>> > Thanks
>
> Nice! Thanks.
> 


0
sandymann2 (1054)
1/22/2008 10:31:58 PM
Reply:

Similar Artilces:

breaking text into multiple cells
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Lets say I type &quot;a b c d&quot; into a cell. Is there a way I can place the cursor between &quot;a&quot; and &quot;b&quot; and then cause only &quot;a&quot; to remain in the original cell while &quot;b c d&quot; move to a new cell? <br><br>Thanks! On 2010-05-27 12:53:39 -0400, Dwade01@officeformac.com said: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel Lets say I type "a b c d" into a cell. Is ther...

negative values causing column title problem
How do I get the Column titles x axis lables at he bottom of the chart field when have negative values It s friday and this is killing me. Select the axis Choose Format>Selected axis On the Patterns tab, for Tick Mark Labels, select Low TFrisch wrote: > How do I get the Column titles x axis lables at he bottom > of the chart field when have negative values > > It s friday and this is killing me. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html hey thanks - boy was thaqt annoying >-----Original Message----- >Select th...

Unsolvable cell naming question: Can anyone beat this one?
I am trying to copy and paste named cells from one workbook to another without having the first workbook file name attach to the named cells. For example, I am copying a cell named XYZ from file One.xls to file Two.xls. I need to have cell XYZ NOT say One.xls!XYZ when it is entered into Two.xls. Also, how can I get that message that asks if I want to use the cell names to not pop up? I think I understand what you're running into here. If the cell you are MOVING refers to a named range in it's source workbook that doesn't exist in the destination workbook, Excel will not ide...

Change the numeric value of a dropdown to text value
I'm trying to email the values of form fields that I have created in a form. So basically what I want to do is in the Subject I want to take the "value" of the dropdown called dropdown1 and put it in the subject. It is working but it is putting the numeric value instead of the text value. How do I convert it. Thanks much! Use the .Result, not the .Value -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Wo...

Exchange 2003 adprep question....Urgent
I currently have a Windows 2000 Server which is a domain controller, and is also running Exchange Server 2003. I'm going to be adding a new Windows 2003 Server (which will also be a domain controller), and will be migrating the existing Exchange Server 2003 to the new server. My question is in the adprep process.... From the research I've done, it appears I should be able to just run the adprep commands on the existing Windows 2000 box, dcpromo the new Windows 2003 Server, install Exchange 2003 on the new server, migrate the mailboxes, and perform the procedure in KB822931 to remove...

Add a goal line to a column chart
I have several column charts which I need to add different goal lines. Any suggestions? The goal has to be represented as a line and not as a column. Thanks for your help! http://peltiertech.com/Excel/Charts/AddLine.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Brandy" <Brandy @discussions.microsoft.com> wrote in message news:EF99E151-13A4-461D-B9CA-DECE4E467904@microsoft.com... >I have several column charts which I need to add different goal lines. Any > sugg...

Visio 2000 (Font Question)
I'm trying to have my text with a black boarder but filled with a different color. How do I do this? eg. outline black, fill red. TIA If you have Office installed, insert a Wordart object into Visio. Randall Arnold "ShipHead" <GoAway@SpamBoy.com> wrote in message news:u7W0b10oDHA.2868@TK2MSFTNGP09.phx.gbl... > I'm trying to have my text with a black boarder but filled with a different > color. > How do I do this? eg. outline black, fill red. > > TIA > > > Select text box; Format | Line, and Format | Fill Pemo -- ,-._|\ / Oz \ ...

Customization Guide Question
Does the customization guide outline how to create and listen for COM Objects to interface your own software to Store Ops. Seems like a lot of hassle to get a copy of it, so want to make sure its worth it. Does it refer to the COM Objects available to developers, and how to use them??? Not many code examples ad the ones there are in VB6, but yes it does define the interface and provide object references. -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all re...

A simple question...
When I enter 1, 2, 3 in a1, a2 & a3, respectively, then select the cells and drag down, the numerical sequence continues (4, 5, 6, etc). However, if I do the same with a, b, c, dragging merely repeats the order (a, b, c, a, b, c) rather than continuing the sequence (d, e, f, etc.). I know I've done this before but I can't remember how. Thanks, silas Try Tools -> Options -> Custom Lists and import the list. You'll need to type it out once and you should be set. -- HTH, Barb Reinhardt "silas" wrote: > When I enter 1, 2, 3 in a1, a2 & a3, re...

Outlook PST question
Hi, I have a user (Boss, actually) and he wants to take his emails home on a CD. (You know what they're like...) The setup here is this : Boss is on a XP laptop PC in the office. Mail server is Exchange, but only internal. Not accesible from outside. (We plan to add MX in May.) External mail is grabbed from the ISP by a POP mail grabber. Outlook account on PC is using cache. (I think I'm right in thinking that if he took his laptop home he would still find his mails on it. Because they're synchronised in an OST ? I think, never tried that.) The only thing I can see him doing ...

Unhiding Protected Rows and Columns? is it possible?
I have a workbook with a number of worksheets that all interlink. I have protected a number of cells in one of the sheets. the problem arrises in that the unprotected cells cannot be hidden or indeed unhidden when the sheet is protected. Is there a way I can protect cells whilst still keeping the formatting ability to widen / hide rows etc. thanks -- guilf ------------------------------------------------------------------------ guilf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25608 View this thread: http://www.excelforum.com/showthread.php?threadid=39...

Gridlines on Column Chart
Hello- I am trying to create a stacked column chart with gridlines that only appear for every 2 columns. How can I do this in Excel 2007? All I can find is how to insert a major gridline between every column. Thanks, Sean Hi, Format the X axis, on the Axis Options section set 'Interval between tickmarks' to 2. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "SeanF74" <SeanF74@discussions.microsoft.com> wrote in message news:E1BE2939-AB52-4FB1-8A0F-ACA9EFDB4CAD@microsoft.com... > Hello- > > I am trying to create a stacked ...

CString to BSTR question dealing with nul characters
I'm creating a C++ DLL for a VB application. I'm using VC7 for creating the DLL application. I'm trying to return a string from this function, here is a simple example extern "C" DLL_API int __stdcall GetString(BSTR* myStr) { int rtnCode = PASS; // PASS is defined as 1 somewhere else CString temp = "Hello"; *str = temp.AllocSysString(); return rtnCode; } >From my test VB 6 app I declare Private Declare Function GetString GetStr "My.dll" (ByRef myStr As String,) As Integer Dim tempStr as String Dim rtnCode as Integer rtnCode = G...

how do you stop editing the text in a created button
i'm in the process of setting up a spreadsheet, which happens to include macros! now the only problem i have is everytime i press the relevant button i've created and assigned a macro to it goes straight in to the editing text mode! is there any way of stopping that! Sounds like a command button from the Control Toolbox. Open the Control Toolbox Toolbar and click on the design button to "exit design mode". This would be the top left button on the toolbar. Gord Dibben Excel MVP On Fri, 18 Nov 2005 08:18:13 -0800, "kara" <kara@discussions.microsoft.com>...

tool tip text in excel button
Hi, I have added button inside excel sheet(not vb control). Is it possible to add tooltip text for this? I am able to set tooltip text for vb controls. But i cannot use these controls inside excel sheet. Thanks and regards Duraiswamy ...

Variable Column Lookup
I have three columns to the left column K each has a header with the year number. 2008, 2009 and 2010. Underneath each of the three columns I have numerical data. On the same header row I have a drop down list in K1. So when I pick 2009, I want the calculations in K2, K3 downward to use the corresponding data in column that has the matching year. 2008 2009 2010 2009 (drop down) 2 3 6 3*10 If I choose the drop down of 2008 then the formula should be 2*10 If I choose the drop down of 2010 then the formula should be 6*10 The actual formula is much more complic...

Stacked Column Charting
Has anyone tried the following? From the data set below, I would like to have the data to the right of the Hrs. avail data be a stacked bar for each day. Along with this the Hrs. avail data would be a bar next to the remaing row of data. I tried placing the Hrs. avail on its own axis but that did not seem to work. In short I want to show the number of hours availabe in a day and actual hours used by the verious other data. Regards, Hal Day Hrs. avail EM's PM's Cal's Projects Other Idle 1 14 4 2.5 2 4 0 1.5 2 14 5 1 1 3 0 4 3 14 0 2 2 6 0 4 4 7 2 0 0 3 0 2 5 14 2 0 0 10 0...

excel 2004 column sorting
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I've read up a bit on others' sorting issues, but haven't come across exactly the same problem as mine. I used to be able to sort the info in one column and the corresponding info in the other columns would sort along with whichever column I chose at the first. Now, my corresponding columns don't sort along with my first sort, resulting in inaccurate info. Also, the workbook I am working with contains macros; the workbook contained macros when I first began using using it, i.e. when sorting was no prob....

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...

CTreeCtrl Question, Adding the '+' by default.
Hi, I have a large tree control, and to save loading time I only load the root items and I only load the child when the user select one of the nodes. The problem is that because the Node has no child the tree control does not draw the "+" to show that there are child nodes. So how can I get the control to display the "+" to expand the node without actually adding child. Many thanks Simon. When creating the TVITEM, set the cChildren member to I_CHILDRENCALLBACK. You will then need to handle the TVN_GETDISPINFO notification. "Simon" <spambucket@myo...

Help with counting user entered text values
Hi, Version: Excel 2000 Situation: In Column G, I will have testers entering in their initals. I would like to display the tester's initals in column H of another worksheet with the number of times that set of intals appeared in column G on column I of the other worksheet. The catch is that I do not have a list of the testers initals ahead of time, so I'll need to pull them from column H. Thanks for any and all help in advance, David Hi David, H10: DAL i10: =COUNTIF(G:G,H10) or if you don't want to see 0 if H10 is empty I10: =IF(H10="","",COU...

Time Format to Text Output
Hi I run Excel 2K I have a series of times that I download from the mainframe. (these are in a date format) EXAMPLE 23/09/2009 6:07:00 AM 22/09/2009 9:22:00 PM 22/09/2009 7:40:00 PM etc etc I am in need of a formula that looks at these times, and based on their values, displays a particular piece of text. (in this case "DAY", "AFT", "NIGHT") Example Any time between the following: 7:20:00 AM to 3:19:00 PM should display the word DAY Any time between the following: 3:20:00 PM to 11:19:00PM should display the word AFT Any time between the following: 1...

Search in multiple columns using VLOOKUP?
Hi, I'm looking for a formula to find a row in a spreadsheet with two matching fields which returns the value of a field in a certain column in that row. VLOOKUP only supports searching in one column as far as I know. Let me give you an example to clarify my request. Please read the following text in a non-proportional font like Courier New. Worksheet A A B C D 0 4 7 0 3 6 1 0 7 5 0 6 Remarks: either column A or B is always zero, and the other one non-zero. I need the formula to use the value of the field which is non-zero. Further on, the value of the column C has to match...

Chart columns
Hi, I am creating a series of charts. I remember someplace to change to shape of the column to round the corners. I can't seem to find at anymore, any help? I am using excel 2000. Thank you for any help you can give me. On Wed, 29 Oct 2003 07:59:37 -0800, rmacneil@comcast.net = <anonymous@discussions.microsoft.com> wrote: > Hi, > I am creating a series of charts. I remember someplace to > change to shape of the column to round the corners. I > can't seem to find at anymore, any help? I am using excel > 2000. Thank you for any help you can give me. I ...

vlookup question.
=VLOOKUP(D84,JobListValues,4) Works fine unless the value I want returned is a blank cell. vlooku returns a 0 and not my blank. So my question is how do I get my blan cell to return in the vlookup instead of the vlookup result of 0. Haven't used vlookup before. :confused ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com IF(VLOOKUP(D84,JobListValues,4)=0,"",VLOOKUP(D84,JobListValues,4)) -- Regards, Peo Sjoblom "IowaBuckMaster" <IowaBu...