Text to Columns

500+ sentences, I need to seperate (text to columns) after the first "-" 
only.  There are subsequent "-" but I only want it seperated at the first. 
And when I seperate it in the second column I do not want a space preceeding 
the text.  Can you give advice on this, any help would be much appreciated!   
Thanks
 
 Sample Data

AIR TAXI - An aircraft operator who conducts operations for hire or 
compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer 
passenger seats and a payload capacity of 7,500# or less. An air taxi 
operates on an on-demand basis and does not meet the "flight scheduled" 
qualifications of a commuter. 
AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority 
to promote the safe, orderly, and expeditious flow of air traffic. 
AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses 
air/ground communications, visual signaling, and other devices to provide ATC 
services to aircraft operating in the vicinity of an airport or on the 
movement area. Authorizes aircraft to land or takeoff at the airport 
controlled by the tower or to transit the Class D airspace area regardless of 
flight plan or weather conditions (IFR or VFR). A tower may also provide 
approach control services (radar or non-radar). 
ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with 
a layer (approximately 5.5% thickness per side) of high-purity aluminum, 
popularly used in airplane manufacture. 
ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice 
radio, this is the current NATO version in global use: 


0
Utf
2/10/2010 6:55:02 PM
excel.programming 6508 articles. 2 followers. Follow

7 Replies
520 Views

Similar Articles

[PageSpeed] 41

You could use this macro  (just set the starting cell and column letters as 
needed in the For Each statements Range reference)...

Sub SplitOnFirstDash()
  Dim Cell As Range, Parts() As String
  For Each Cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    Parts = Split(Cell.Value, "-", 2)
    Cell.Offset(0, 1).Value = Trim(Parts(1))
    Cell.Value = Trim(Parts(0))
  Next
End Sub

-- 
Rick (MVP - Excel)


"LeisaA" <LeisaA@discussions.microsoft.com> wrote in message 
news:7A3C438A-BB14-4EBA-AFA2-5D0C46E495BE@microsoft.com...
> 500+ sentences, I need to seperate (text to columns) after the first "-"
> only.  There are subsequent "-" but I only want it seperated at the first.
> And when I seperate it in the second column I do not want a space 
> preceeding
> the text.  Can you give advice on this, any help would be much 
> appreciated!
> Thanks
>
> Sample Data
>
> AIR TAXI - An aircraft operator who conducts operations for hire or
> compensation in accordance with FAR Part 135 in an aircraft with 30 or 
> fewer
> passenger seats and a payload capacity of 7,500# or less. An air taxi
> operates on an on-demand basis and does not meet the "flight scheduled"
> qualifications of a commuter.
> AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate 
> authority
> to promote the safe, orderly, and expeditious flow of air traffic.
> AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
> air/ground communications, visual signaling, and other devices to provide 
> ATC
> services to aircraft operating in the vicinity of an airport or on the
> movement area. Authorizes aircraft to land or takeoff at the airport
> controlled by the tower or to transit the Class D airspace area regardless 
> of
> flight plan or weather conditions (IFR or VFR). A tower may also provide
> approach control services (radar or non-radar).
> ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad 
> with
> a layer (approximately 5.5% thickness per side) of high-purity aluminum,
> popularly used in airplane manufacture.
> ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
> radio, this is the current NATO version in global use:
>
> 

0
Rick
2/10/2010 7:31:10 PM
Check one of your other posts.

LeisaA wrote:
> 
> 500+ sentences, I need to seperate (text to columns) after the first "-"
> only.  There are subsequent "-" but I only want it seperated at the first.
> And when I seperate it in the second column I do not want a space preceeding
> the text.  Can you give advice on this, any help would be much appreciated!
> Thanks
> 
>  Sample Data
> 
> AIR TAXI - An aircraft operator who conducts operations for hire or
> compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer
> passenger seats and a payload capacity of 7,500# or less. An air taxi
> operates on an on-demand basis and does not meet the "flight scheduled"
> qualifications of a commuter.
> AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority
> to promote the safe, orderly, and expeditious flow of air traffic.
> AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
> air/ground communications, visual signaling, and other devices to provide ATC
> services to aircraft operating in the vicinity of an airport or on the
> movement area. Authorizes aircraft to land or takeoff at the airport
> controlled by the tower or to transit the Class D airspace area regardless of
> flight plan or weather conditions (IFR or VFR). A tower may also provide
> approach control services (radar or non-radar).
> ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with
> a layer (approximately 5.5% thickness per side) of high-purity aluminum,
> popularly used in airplane manufacture.
> ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
> radio, this is the current NATO version in global use:

-- 

Dave Peterson
0
Dave
2/10/2010 7:32:03 PM
Hi  LeisaA

In Excel 2007 I used the nested cell fuction below to get everything
rigt of the first dash.

=MID(A1,SEARCH("-",A1,1)+2,LEN(A1))

You can olso use the function below:


Function RightOfFirst(strOriginal As String, strDelimiter) As String
    Dim intPos As Integer

    intPos = InStr(1, strOriginal, strDelimiter, vbTextCompare) + 2
    If intPos > 0 Then
        RightOfFirst = Mid(strOriginal, intPos)
    Else
        RightOfFirst = strOriginal
    End If

End Function


Function LeftOfFirst(strOriginal As String, strDelimiter) As String
    Dim intPos As Integer

    intPos = InStr(1, strOriginal, strDelimiter, vbTextCompare) + 2
    If intPos > 0 Then
        LeftOfFirst = Left(strOriginal, InpOs)
    Else
        LeftOfFirst = strOriginal
    End If

End Function



HTH,

Wouter.
0
Wouter
2/10/2010 7:54:09 PM
Here is a sample macro for column A split into A & B:

Sub SplitThem()
Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each rr In r
    v = rr.Value
    n = InStr(v, "-")
    rr.Value = Left(v, n - 1)
    rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
Next
End Sub

-- 
Gary''s Student - gsnu201001


"LeisaA" wrote:

> 500+ sentences, I need to seperate (text to columns) after the first "-" 
> only.  There are subsequent "-" but I only want it seperated at the first. 
> And when I seperate it in the second column I do not want a space preceeding 
> the text.  Can you give advice on this, any help would be much appreciated!   
> Thanks
>  
>  Sample Data
> 
> AIR TAXI - An aircraft operator who conducts operations for hire or 
> compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer 
> passenger seats and a payload capacity of 7,500# or less. An air taxi 
> operates on an on-demand basis and does not meet the "flight scheduled" 
> qualifications of a commuter. 
> AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority 
> to promote the safe, orderly, and expeditious flow of air traffic. 
> AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses 
> air/ground communications, visual signaling, and other devices to provide ATC 
> services to aircraft operating in the vicinity of an airport or on the 
> movement area. Authorizes aircraft to land or takeoff at the airport 
> controlled by the tower or to transit the Class D airspace area regardless of 
> flight plan or weather conditions (IFR or VFR). A tower may also provide 
> approach control services (radar or non-radar). 
> ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with 
> a layer (approximately 5.5% thickness per side) of high-purity aluminum, 
> popularly used in airplane manufacture. 
> ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice 
> radio, this is the current NATO version in global use: 
> 
> 
0
Utf
2/10/2010 8:16:01 PM
> rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))

Two things about about the above line of code....

First, you can remove the Trim function call since you accounted for the 
leading space when you used n+1 for the Mid function's second argument. 
Second, you can remove the 9999 (third) argument from the Mid function 
call... unlike the worksheet function's MID function, in the VBA one the 
third argument is optional and, when you omit it, it automatically returns 
all the remaining chars (after the position specified by the second 
argument).

-- 
Rick (MVP - Excel)


"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
news:3AA180B8-2E00-404A-89C5-23D233A99E0A@microsoft.com...
> Here is a sample macro for column A split into A & B:
>
> Sub SplitThem()
> Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
> For Each rr In r
>    v = rr.Value
>    n = InStr(v, "-")
>    rr.Value = Left(v, n - 1)
>    rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
> Next
> End Sub
>
> -- 
> Gary''s Student - gsnu201001
>
>
> "LeisaA" wrote:
>
>> 500+ sentences, I need to seperate (text to columns) after the first "-"
>> only.  There are subsequent "-" but I only want it seperated at the 
>> first.
>> And when I seperate it in the second column I do not want a space 
>> preceeding
>> the text.  Can you give advice on this, any help would be much 
>> appreciated!
>> Thanks
>>
>>  Sample Data
>>
>> AIR TAXI - An aircraft operator who conducts operations for hire or
>> compensation in accordance with FAR Part 135 in an aircraft with 30 or 
>> fewer
>> passenger seats and a payload capacity of 7,500# or less. An air taxi
>> operates on an on-demand basis and does not meet the "flight scheduled"
>> qualifications of a commuter.
>> AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate 
>> authority
>> to promote the safe, orderly, and expeditious flow of air traffic.
>> AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
>> air/ground communications, visual signaling, and other devices to provide 
>> ATC
>> services to aircraft operating in the vicinity of an airport or on the
>> movement area. Authorizes aircraft to land or takeoff at the airport
>> controlled by the tower or to transit the Class D airspace area 
>> regardless of
>> flight plan or weather conditions (IFR or VFR). A tower may also provide
>> approach control services (radar or non-radar).
>> ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad 
>> with
>> a layer (approximately 5.5% thickness per side) of high-purity aluminum,
>> popularly used in airplane manufacture.
>> ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
>> radio, this is the current NATO version in global use:
>>
>> 

0
Rick
2/10/2010 8:27:41 PM
Thanks for the info!
-- 
Gary''s Student - gsnu201001


"Rick Rothstein" wrote:

> > rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
> 
> Two things about about the above line of code....
> 
> First, you can remove the Trim function call since you accounted for the 
> leading space when you used n+1 for the Mid function's second argument. 
> Second, you can remove the 9999 (third) argument from the Mid function 
> call... unlike the worksheet function's MID function, in the VBA one the 
> third argument is optional and, when you omit it, it automatically returns 
> all the remaining chars (after the position specified by the second 
> argument).
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
> news:3AA180B8-2E00-404A-89C5-23D233A99E0A@microsoft.com...
> > Here is a sample macro for column A split into A & B:
> >
> > Sub SplitThem()
> > Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
> > For Each rr In r
> >    v = rr.Value
> >    n = InStr(v, "-")
> >    rr.Value = Left(v, n - 1)
> >    rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
> > Next
> > End Sub
> >
> > -- 
> > Gary''s Student - gsnu201001
> >
> >
> > "LeisaA" wrote:
> >
> >> 500+ sentences, I need to seperate (text to columns) after the first "-"
> >> only.  There are subsequent "-" but I only want it seperated at the 
> >> first.
> >> And when I seperate it in the second column I do not want a space 
> >> preceeding
> >> the text.  Can you give advice on this, any help would be much 
> >> appreciated!
> >> Thanks
> >>
> >>  Sample Data
> >>
> >> AIR TAXI - An aircraft operator who conducts operations for hire or
> >> compensation in accordance with FAR Part 135 in an aircraft with 30 or 
> >> fewer
> >> passenger seats and a payload capacity of 7,500# or less. An air taxi
> >> operates on an on-demand basis and does not meet the "flight scheduled"
> >> qualifications of a commuter.
> >> AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate 
> >> authority
> >> to promote the safe, orderly, and expeditious flow of air traffic.
> >> AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
> >> air/ground communications, visual signaling, and other devices to provide 
> >> ATC
> >> services to aircraft operating in the vicinity of an airport or on the
> >> movement area. Authorizes aircraft to land or takeoff at the airport
> >> controlled by the tower or to transit the Class D airspace area 
> >> regardless of
> >> flight plan or weather conditions (IFR or VFR). A tower may also provide
> >> approach control services (radar or non-radar).
> >> ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad 
> >> with
> >> a layer (approximately 5.5% thickness per side) of high-purity aluminum,
> >> popularly used in airplane manufacture.
> >> ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
> >> radio, this is the current NATO version in global use:
> >>
> >> 
> 
> .
> 
0
Utf
2/10/2010 10:10:01 PM

"Rick Rothstein" wrote:

> > rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
> 
> Two things about about the above line of code....
> 
> First, you can remove the Trim function call since you accounted for the 
> leading space when you used n+1 for the Mid function's second argument. 
> Second, you can remove the 9999 (third) argument from the Mid function 
> call... unlike the worksheet function's MID function, in the VBA one the 
> third argument is optional and, when you omit it, it automatically returns 
> all the remaining chars (after the position specified by the second 
> argument).
> 
> -- 
> Rick (MVP - Excel)
> 
> 
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message 
> news:3AA180B8-2E00-404A-89C5-23D233A99E0A@microsoft.com...
> > Here is a sample macro for column A split into A & B:
> >
> > Sub SplitThem()
> > Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
> > For Each rr In r
> >    v = rr.Value
> >    n = InStr(v, "-")
> >    rr.Value = Left(v, n - 1)
> >    rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
> > Next
> > End Sub
> >
> > -- 
> > Gary''s Student - gsnu201001
> >
> >
> > "LeisaA" wrote:
> >
> >> 500+ sentences, I need to seperate (text to columns) after the first "-"
> >> only.  There are subsequent "-" but I only want it seperated at the 
> >> first.
> >> And when I seperate it in the second column I do not want a space 
> >> preceeding
> >> the text.  Can you give advice on this, any help would be much 
> >> appreciated!
> >> Thanks
> >>
> >>  Sample Data
> >>
> >> AIR TAXI - An aircraft operator who conducts operations for hire or
> >> compensation in accordance with FAR Part 135 in an aircraft with 30 or 
> >> fewer
> >> passenger seats and a payload capacity of 7,500# or less. An air taxi
> >> operates on an on-demand basis and does not meet the "flight scheduled"
> >> qualifications of a commuter.
> >> AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate 
> >> authority
> >> to promote the safe, orderly, and expeditious flow of air traffic.
> >> AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
> >> air/ground communications, visual signaling, and other devices to provide 
> >> ATC
> >> services to aircraft operating in the vicinity of an airport or on the
> >> movement area. Authorizes aircraft to land or takeoff at the airport
> >> controlled by the tower or to transit the Class D airspace area 
> >> regardless of
> >> flight plan or weather conditions (IFR or VFR). A tower may also provide
> >> approach control services (radar or non-radar).
> >> ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad 
> >> with
> >> a layer (approximately 5.5% thickness per side) of high-purity aluminum,
> >> popularly used in airplane manufacture.
> >> ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
> >> radio, this is the current NATO version in global use:
> >>
> >> 
> 
> .
> Sorry, I am not understanding how to use this function?
0
Utf
2/10/2010 10:42:01 PM
Reply:

Similar Artilces:

Exchange rich-text format
What are the results from the client side if I change my Exchange 2003 server to "Alwayes Use" Exchange Rich-Text format from "Determined by individual user settings"? On Thu, 22 Jun 2006 08:31:01 -0700, CK <CK@discussions.microsoft.com> wrote: >What are the results from the client side if I change my Exchange 2003 server >to "Alwayes Use" Exchange Rich-Text format from "Determined by individual >user settings"? Depends on what your users are sending messages as. Most will be using HTML or RTF anyway unless you have some policy in p...

How to remove the alternative text box from the publisher email?
Hi, I have created a publisher email and have email it to myself for testing view. I find there is a small alternative text box appearing whenever my mouse cursor station at any point of the email. How to remove this alternative text box from the publisher email? Help... -- Thank you, Cpviv Sounds like all the text has was converted to an image. Try to select the text and you will see it is an image. Go to tools > Options > Web tab and under Email options uncheck the option to send as an image. If that doesn't fix the problem, reference: Tips and troubleshooting for ...

Change from MS Outlook Rich Text to HTML
I use Outlook 98 and I want to change my message format to HTML so that I can have nice backgrounds etc on my mail. For some reason this facility doesn't seem to be available to me on the 'Mail Format' Tab. It is blacked out and I cannot change it from Rich Text to HTML. Is there anything I can do to sort this out? Could it be because I am on a network at work and they have disabled it? If so, how can I enable it? Cheers! ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www...

Trouble with hyhens within text when using LOOKUP
I have two columns, each containing a list of part numbers. Some of the part numbers contain hyphens. I am using LOOKUP and/or VLOOKUP to determine if the value in one column exists in the other. This works great on non-hyphenated part numbers. However, it will not find or return the hyphenated part numbers from the specified arrays. As a test, I did a quick if statement to compare the instances of identical hyphenated values that exist in both columns. Those statements did not have a problem with the hyphens. Can anyone offer any help? If hyphens cannot be used in conjunction with the ...

How to use MSExcel to plot Column A against Col B?
I want to plot col A (x axis) against col B ( y axis). I cant seem to do it. Can anyone here please give me step by step primer. Thanks in advance. All I get is the graph of (1,2,............n) (x axis) against the n values of either col A or B. i.e 2 graphs instead of one. (In the old Lotus this was so simple: select the column for the X axis and then select the col for the Y and press enter, and you'd get the chart) Why is it so diff in Excel? Select the entire range you want to graph such as a2:b44>insert>chart>>>> -- Don Guillett SalesAid Software dguillett1@au...

instructions disppear when users begin type (text field)
Hi all, I need to customize the outlook contact form and I want to add one text field to allow users to add details info and instruct users how to add. Instructions shows in the field and the instructions disappear when users click and begin to type. How should I do this? exchange 2003/outlook2003 Thank you. It's hard for me to visualize exactly what you're expecting to happen. If you want the instructions to stay on the screen, you could display them in a label control. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook 2007 Programming: Jumps...

If statement with formatted text
Hi, Is there any way to have an if formula such as: If (A1="Active", "KAE",KPE") where the two letters after the K are formatted as subscript? The best I've done is to paste a picture over the cell. The picture's formula refers to named formula that selects one of two cells, the one with correct string. One cell contains KAE and the other KPE with the subscript. However, it means that I'm using a picture and it would be much nicer if I could just do it in an If formula. I hope that makes some kind of sense, and thanks in advance for your help. D...

Printing
Hope you folks can help me out with a strange one. I have several worksheets formatted in exactly the same way as follows: Col A - width 4 Col B - hidden Col C - width 4 Col D - Width 108 Col E - Width 3 Col F - Width 11 Col G - Hidden Col H - Width 11 & Empty My print range should be Cols A:G (I have used page setup to set the scaling to fit 1 page wide by [blank] pages tall, thus each sheet will print as many pages as required depending on number of rows] When I have the print range set to A:G only columns A:E show on the print preview (and also on the actual print out) and when I m...

Saving html message as Draft changes text formatting...
WIN XP HE, OL 2002 Hi, I have recently noticed that whenever I write an email (using Word as editor) in html format, and instead of sending it, save it (to the drafts folder), the text itself changes format from my default to another one. It seems to change in the paragraph style which then changes the text format. The only change I recently made was to edit my signatures in html, rtf and plain text format. When I write a new email, it opens up with the signature already in it and perhaps there are format/style conflicts..? Tx for shedding some light into this. S As an added information, t...

can lookup return cell reference istead of "text" for sumif?
I am trying to use a lookup-function to determine a different sum range for several criteria. Like so: =Sumif($A$7:$A$1447;"<"&X3;vlookup(e3;AT3:AU11;2;false)-Sumif($A$7:$A $1447;"<"&y3;vlookup(e3;AT3:AU11;2;false) The problem is that the vlookup returns text and not the cell reference. Is there a way to get the answer from the lookup expressed as cell reference instead of text, since sumif can't use text, just the cell reference? I use it to calculate the number of hours the staff should be paid, so it's different from weekdays to saturdays, holidays...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

cannot view all of text in large cell, even though I have it to w.
I have cell format to wrap text and it works fine to a p[oint then no more text is displayed....casn increase the size of the cell, but still only so much will display....rest of the cell show blank. Hi +the limit is 1024 characters. You can extend this with manually inserting linebrekas using aLT+ENTER -- Regards Frank Kabel Frankfurt, Germany sydme wrote: > I have cell format to wrap text and it works fine to a p[oint then no > more text is displayed....casn increase the size of the cell, but > still only so much will display....rest of the cell show blank. ...

Text box jumps to left of page
Word 2004 (I am relatively new to Word and am delighted to find a forum specifically for the Mac version. There are a number of unresolved, niggling issues I can live however they slow the workflow. I am eager to learn.) In the recent past, I manually converted 12,000+ recipes from WordPerfect 7 to Word. Since Word 2004 does not have a filter for the old files, the conversion was done on the Windows side of my Mac in Word2003. Those files _usually_ open without protest also in 2004. One annoyance regards text boxes. When text was highlighted and a text box was requested for it in...

initial default column width
Is there a way to configure Excel 2000 so that when I create a new Workbook or add a new Worksheet so that all the columns have a particular width instead of the default 64 pixels? TIA Create the workbook exactly the way you want it, then save it as a template with the name "Book.xlt" (no quotes) in you XLStart directory. It'll be then used as the template for new workbooks. Likewise, save a one-sheet workbook as a template, named "Sheet.xlt" for the template for Insert/Worksheet. In article <419E181F.6251D20D@nospam.net>, Bruceh <bruce@nospam.net&...

Windows and Mac have two distinctly different units for column width.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I am trying to format columns for some data I am entering in a spreadsheet and when I enter &quot;15.00&quot;, which is the required width for these columns given by my professor, I end up with a column 15 inches wide. What I would prefer is for the options to be like the default options in Windows version of Excel. In Windows version of Excel, when you hover over the lines between the columns it gives you two numbers (e.g. 8.43 (64 pixels)). These are the default numbers for column width in Windows Excel....

How did you add text into publisher, without using boxes?
how do you add text to publisher without using text boxes I suppose you could create your text as an image and insert the image into your publisher file. -- Don Vancouver, USA "Calvin Scott" <Calvin Scott@discussions.microsoft.com> wrote in message news:64D23D52-138D-47B4-B265-4A41BF14BF55@microsoft.com... > how do you add text to publisher without using text boxes Calvin Scott <Calvin Scott@discussions.microsoft.com> was very recently heard to utter: > how do you add text to publisher without using text boxes You don't. Text in Publisher has to e...

How to assign unique number to column duplicates?
Hi All, I need to assign a unique number to a set of duplicates all in one column in Excel 2007. so columnA will has about 9000 numbers, some of them unique, and others are duplicates of 2-4 approx. I used to conditional formatting to show which are duplicates, but need to be able to assign a unique number to each set duplicates, that will be in sequential order... e.g. ColumnA ColumnB(unique ID) 01233 0001 01233 0001 01234 - 01255 0002 01255 0002 etc.... Any ideas please? I don't know how to do programming, just form...

Create your personal SMS homepage, let friends send FREE text message to your mobile phone by web
Create your personal SMS homepage, let friends send FREE text message to your mobile phone by web Support over 147 countries and ALL languages (unicode encoding) MSG.to provides a unique personal SMS URL for you. It is easy for friends sending text message to you by web. For example, if your name is Lisa, you can register: http://MSG.to/Lisa (you can use this link to see demo) You can also register your mobile phone number as SMS URL like: http://MSG.to/+886913123456 (you can use this link to see demo) Please visit http://msg.to/ for more information. Related keywords: Short Message Serv...

imbed excel cell text in a shape
I need to insert a number into a shape that corresponds to a cell in excel. Kinda like the exploded parts view in a car repair manual. A callout shape with a text value "123" that is linked to cell A1 in file ***.xls. It would be nice to be able to auto generate the callouts from the excel file. shape 123 is cell A1, shape 124 is cell A2 and so on. Thanks what version of visio are you running? al "mystified" <mystified@discussions.microsoft.com> wrote in message news:80B4E390-190A-41D9-AC4A-F96C1DA6FB6E@microsoft.com... >I need to insert a number into a s...

Filter two columns with criterion applying to one or the other?
Hi, I am looking for a solution to the following filtering problem: I have two adjacent columns, so using a filter for both of them is no problem. But what I want to do and don't know how to do is this: I want to filter for values greater than x (a certain number, in my case 5000) in any of the two columns. I can filter both columns for x greater than 5000 but that filters out more than I want because there may be some cells with a value greater than 5000 in only one of the two columns. Is there a solution to this problem (using Excel alone or an add-on)? Peter Hi Peter you can use th...

Vlookup, multiple times in one column summing corresponding
I have a list that contains common invoice numbers that appear multipl times in column A and need to use the vlookup function to find and su the corresponding amounts in column B. As you would be aware the following vlookup functio =VLOOKUP(A3,DATA,2,FALSE) will only return the one amount once i reaches a match and I need it to continue down the column and sum al matching invoice numbers. Column A Column B 145768 356.87 145769 678.90 145880 80.87 * 145769 103.55 145770 56.90 145769 78.32 145880 54.09 * The answer I�m looking to return is 145880 134.96 It has to be a vlookup sty...

Extract Values from a Column
I have a worksheet used to track time spent on various jobs. One column is for the JOB # while others are for descriptions, etc. I would like to have formulas or possible a macro (if necessary) to sum the time spent on different jobs. For example, say (for simplicity) that each row is equal to 1 unit of time. Then in this row I put 342 for JOB # 342. After the entire day I have worked on say 5 different jobs. I would like to have a cell that says "Total time spent on Job # 342" and then next to it a formula that would look at the column and count all the values that are equal t...

Text box unwanted border
For some reason I know not why, that even though I enter 'No line' in both Line/Border style and Dash style I still have a visible bold dash surrounding the text box. Any help would be appreciated. Did you happen to draw a rectangle around the text box? Try highlighting it, and move the bottom text box adjustment up. If the dashed line didn't move, click on it and delete. Or, Copy the text, and paste it and if the dashed line didn't go with the copy, delete the original. -- Don Vancouver, USA "Meridklt" <Meridklt@discussions.microsoft.com> wrote in m...

HQ Reports
In the HQ report 'Work Orders - Detailed' one of the default columns is Order #. I am hoping to enable the magnifying glass on this field that would take me straight to the journal for that transaction. I do have my journals being uploaded. Is this a reasonable wish? Can you point me in the right direction? Thanks, Tom -- The worst words in business: "We''ve always done it that way" -- Stop Fishing for eMail. TT, Try; DrillDownFieldName = "[Order].ID" In the Order # column section. -- * Get Secure! - www.microsoft.com/security You must be usi...

Can I add more columns to a spread sheet or is limit 256 columns
I am trying to produce an Excel spread sheet with at least 370 columns in one spread sheet but the limit seems to be set at 256. Is it possible to increase this number? Grit your teeth and wait for the next version of XL (12), and you'll have all you need!<g> Check out the new specs here: http://blogs.msdn.com/excel/default.aspx -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Pi...