Truncating cells

Hello,
I have a spreadsheet full of student names (Last, First, Middle) each in a 
separate column.  I need to shorten the Middle Name column down to just the 
Middle Initial (1 character).  It would be nice to do these using a function 
rather that edit them individually.

I've tried the truncate function but could not get that to work.  Any 
suggestions?

Thanks,
Matt Verdill 


0
mverdill (5)
10/12/2005 7:25:01 PM
excel 39879 articles. 2 followers. Follow

4 Replies
869 Views

Similar Articles

[PageSpeed] 4

Greetings,

You need to use the LEFT function. In a new cell, type this formula:

=LEFT(A1,1)

Where A1 is the cell with the Middle Name and 1 is the number of characters.

James Walker, Jr.


"Matt Verdill" <mverdill@punxsy.k12.pa.us> wrote in message 
news:%23ysfQI2zFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a spreadsheet full of student names (Last, First, Middle) each in a 
> separate column.  I need to shorten the Middle Name column down to just 
> the Middle Initial (1 character).  It would be nice to do these using a 
> function rather that edit them individually.
>
> I've tried the truncate function but could not get that to work.  Any 
> suggestions?
>
> Thanks,
> Matt Verdill
> 


0
walker1 (2)
10/12/2005 7:27:23 PM
=left(b2,1)

and drag down should give you the first character in cell B2.

Matt Verdill wrote:
> 
> Hello,
> I have a spreadsheet full of student names (Last, First, Middle) each in a
> separate column.  I need to shorten the Middle Name column down to just the
> Middle Initial (1 character).  It would be nice to do these using a function
> rather that edit them individually.
> 
> I've tried the truncate function but could not get that to work.  Any
> suggestions?
> 
> Thanks,
> Matt Verdill

-- 

Dave Peterson
0
petersod (12004)
10/12/2005 7:30:05 PM
Matt,

I think I can handle this...

LEFT(A1,1)

If you need the period at the end its

=LEFT(A1,1)&"."

Beege.


"Matt Verdill" <mverdill@punxsy.k12.pa.us> wrote in message 
news:%23ysfQI2zFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a spreadsheet full of student names (Last, First, Middle) each in a 
> separate column.  I need to shorten the Middle Name column down to just 
> the Middle Initial (1 character).  It would be nice to do these using a 
> function rather that edit them individually.
>
> I've tried the truncate function but could not get that to work.  Any 
> suggestions?
>
> Thanks,
> Matt Verdill
> 


0
bwgilman1 (10)
10/12/2005 7:33:36 PM
Insert a "helper" column between B and C.
Select Column B with the Middle names, then:
<Data> <TextToColumns>
Click "Fixed Width", then <Next>

Click in the preview window to create a "break line", and drag the line to
separate the first initial from the rest of the name.
Then, <Finish>

You now have Column B filled with *only* the first initial, and this is
actual text, *not* a formula returning text.

You can delete the "helper" Column C if you wish.
-- 
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Matt Verdill" <mverdill@punxsy.k12.pa.us> wrote in message
news:%23ysfQI2zFHA.1252@TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a spreadsheet full of student names (Last, First, Middle) each in a
> separate column.  I need to shorten the Middle Name column down to just
the
> Middle Initial (1 character).  It would be nice to do these using a
function
> rather that edit them individually.
>
> I've tried the truncate function but could not get that to work.  Any
> suggestions?
>
> Thanks,
> Matt Verdill
>
>

0
ragdyer1 (4060)
10/12/2005 7:47:01 PM
Reply:

Similar Artilces:

insert memo feild into a memo field truncates at 255 characters -
Greetings I have a table that has a memo field to retain specific information - text - which is the base of the combo box. Via a combobox on a form this memo field is added to a forms field which is also sized as a memo called "Project_Notes" - this part works. At a certain time the user uploads this form and its feilds to a parent table which also has a memo field to recieve the data from the other memo field. 'we have created the new entry now we should update the notes strOtherFields = ",Action_By,To_Do_date" _ ...

VBA
I have created code that inserts lines into a financial statement but need to have certain cells in other code stay constant. B1 in certain code must stay at B1 regardless of changes to the spreadsheet. Any suggestions?? --- Message posted from http://www.ExcelForum.com/ Use the INDIRECT function. E.g., =INDIRECT("B1") The B1 will remain so regardless of inserting and deleting rows. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "bforster1 >" <<bforster1.1cmxzp@excelforum-nospam.com> wrote in m...

How to split cell based on capitalised suburb name
Hi I have data such as the following in a single cell per line Level 8, 160 Marsden St, PARRAMATTA 15 Carter Street, HOMEBUSH BAY 223 - 239 Liverpool Road, ASHFIELD I want to put the Suburb name into a seperate cell without going through hundreds of rows of data manually. There can be spaces in the suburb name and multiple commas in the field. The only way to differentiate the suburb name is that it is in UPPERCASE. Does anyone know a formula or macro that could complete this? Thanks Check out whether the below would help. The below will extract the information af...

next cell
if i have cell a1 selected in visual basic code what is the line of code to select the cell below it......ie add one to the row number? im sure this is simple!! cheers robert --- Message posted from http://www.ExcelForum.com/ one way: ActiveCell.Offset(1, 0).Select In article <robert_woodie.11228s@excelforum-nospam.com>, robert_woodie <<robert_woodie.11228s@excelforum-nospam.com>> wrote: > if i have cell a1 selected in visual basic code what is the line of code > to select the cell below it......ie add one to the row number? > > im sure this is simple...

Text cells converts to dates
Hello, I have formatted the cells as "text". When I type in data, Excel leaves the numbers as is but when I copy and paste data from the web, it keeps converting the numbers to a date. For example, I can type "11-9 7-13" and the data stays as is BUT when I copy "11-9 7-13" from two columns on a web page and paste it, the data is converted to "9-Nov 13-Jul". What do I have to do to make Excel display those numbers without converting to a date? Apparently, formatting the cells as "text" does not work with pasting. Thanks. Obvio...

Gantt Bar truncated
Quick search ends in no joy.... I had a fellow scheduler drop buy with a question. He has inherited a Project (2003) file. The task bars in the gantt chart view do not reflect the "start" and "finish" dates in the start and finish column. When the dates are added to each end of the bar in the gantt chart view, the task bar extends from the start date but does not stretch all the way out the planned finish date. I did a cursory view of the file on my way out and there was nothing readily apparent that would be driving this condition. I'm new to MS Project...

split contents in one cell into two cells
i have data in a microsoft word document that is 2 long columns, seperated by a space. when i copy and paste this into Excel, it copies it into 1 column with a space between the numbers. how can i split this one column into 2 different columns? Hi egoldwyn Use Data>Text to columns in the menu bar -- Regards Ron de Bruin http://www.rondebruin.nl "egoldwyn" <egoldwyn@discussions.microsoft.com> wrote in message news:78393FA1-FC1D-45DE-9BB9-C580884592FF@microsoft.com... >i have data in a microsoft word document that is 2 long columns, seperated by > a space. wh...

Problem with combining cells
Hi, Am wondering if anyone has tried this before. I would like to combine the contents of two cells into one. Eg: Cell C3 = 25 and cell D3= kg/m3. The number 3 in cell D3 is in subscript format. However, when I combine the cells using "Concatenate" or "&" function, the formatting for the subscript is lost. Is there anyway I can do this? ;-D One way would be to Copy > PasteSpecial > Values on the cell and then re-format for the superscript 3 Vaya con Dios, Chuck, CABGx3 "yhm" <yhm@discussions.microsoft.com> wrote in message news:6...

change size of a cell
I am using Excel 2003. Is there a way to change the size of a cell without changing the surrounding columns and rows? No, But you can drag a text box from the drawing toolbar>select it>in the formula bar type =f11 and the contents of cell f11 will be displayed. -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Amy J." <AmyJ@discussions.microsoft.com> wrote in message news:B69EA363-9513-4C3A-BC1F-AA95716C7EDB@microsoft.com... >I am using Excel 2003. Is there a way to change the size of a cell without > changing the surrounding columns and rows? No...

Dynamic # of Destination Cells
Is it possible to have a dynamic number of destination cells? For example A B C D 1 Principle Interest # of Cycles 2 100 1.03 11 3 4 So that for each interest cycle a new principle is calulated and output to B7, B8, B9 ... b17 or B7, B8, B9 ...B22 if 16 were the value for C2. And if that is possible can you make a chart using dynamic cell ranges? Thanks TJ TJ Life often turns on such small things as a flickering oil lamp. - Scheherezade - Maybe you can modify your formulas: =IF($C$2-ROW()+ROW($B$7)<=0,"","you...

Adding cells with different colors
If you have a row with different numbers in the cells, and then giv them different colors, is it possible to make some kind of formula where you can add all cells which is yellow, red or blue, by the selves (i.e. A1 and A4 are yellow, so they will added - A2 and A7 ar red, so they will be added etc) ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You need to use a VBA function to sum by colors. See www.cpearson.com/excel/colors.htm for details and example code. -- Cordi...

one content of cell to many row of cells
I have two list of data. eg 1 A 2 B 3 C I want to create two list of data like following 1 A 1 B 1 C 2 A 2 B 2 C 3 A 3 B 3 C the number of cells in each list maybe vary and I don't care about the location of cells. I am looking for a method to do that other than copy and past. Thank you very much Lets say in A1 thru B10 we have: 1 a 2 b 3 c 4 d 5 e 6 f 7 g 8 h 9 i 10 j Enter and run this small macro: Sub Macro1() k = 1 For i = 1 To 10 v = Cells(i, 1) For j = 1 To 11 If j = 11 Then Else Cells(k, 3) = v Cells(k, 4) = Cells(j, 2) End If...

deleting nonadjacent cells
i know that i can select nonadjacent cells, but i can't seem to find a way to delete them. i have either 3 columns of names, some duplicates, from which i'd like to get a list of all the names only once, without duplicates. i tried moving with unique cells only. it didn't work. so i highlighted each list in a different color and placed them all in one column and sorted them. now i have all the duplicate names in order and in a different color. i either want to delete selected non-adjacent cells, or find a way to go through that list and filter out all the duplicates. i&#...

Clear cells
Excel 2003 I have a column of cells that are all drop down lists. I want a button that clears all selected values in that range of cells. How can I do that? Thanks Dave OK - I guess I can do it with a macro but how do I make a nice button? Dave "Dave" <dcooper@iacnc.edu> wrote in message news:F21E3B1B-597E-4806-9326-21D35D8E902F@microsoft.com... > Excel 2003 > > I have a column of cells that are all drop down lists. > > I want a button that clears all selected values in that range of cells. > > How can I do that? > > Thanks > Dave Sho...

VBA to change format of a linked cell
I apologize if this is really simple, but I've been searching around on the web and haven't found what I am looking for.... Maybe I am not searching for the right thing..... I would like to know the VBA syntax that I could use to change the font format of a linked cell to be the same as the cell it is linked to... A simple example: on sheet2, in cell b13, is the value 2. It is colored blue and is bolded. on sheet2, in cell b47, is the value 5. it is formatted in the default manner cell A1, on the sheet named sheet1, contains the formula ='sheet2!b13' cell A2, on the sheet ...

Finding Value of a Cell in a Range
In cell C1 I want to place a formula that will return a value of 1 if the value of cell A1 is contained in the range of cells B1:B127 and a value of 0 is the value of A1 is not found in the range of cells B1:B127. All values are text. =IF(COUNTIF(B:B,1)>0,1,0) or use your b1:b127 instead -- Don Guillett SalesAid Software donaldb@281.com "Wendy L" <puddytat_99@hotmail.com> wrote in message news:eyJFRYEYEHA.716@TK2MSFTNGP11.phx.gbl... > In cell C1 I want to place a formula that will return a value of 1 if the > value of cell A1 is contained in the range of cells B1:...

Executing a macro from a cell
1. There is some way to make that excel, starting from a conditional structure in a cell, execute automatically a macro??? Something like this: A B 1 2 1 =if(A2=1,macro1(),macro2()) No, functions can return a value, they cannot change the format or run a macro. You can use event code to do it, but not a worksheet function. -- HTH RP (remove nothere from the email address if mailing direct) "filo666" <filo666@discussions.microsoft.com> wrote in message news:122D7DF6-CF7B-4AFB-A6F7-5F55DA090A86@microsoft.com... > 1. There is some way to make that excel, starting ...

Printing truncated
Mr.Bob Philips, I need your help.Plese refer to my post "Printing Formulas" dated 16/Nov/05.I posted a reply to this answer ,your help is very much needed. You have another reply at that thread. TUNGANA KURMA RAJU wrote: > > Mr.Bob Philips, > I need your help.Plese refer to my post "Printing Formulas" dated > 16/Nov/05.I posted a reply to this answer ,your help is very much needed. -- Dave Peterson ...

HOW TO APPLY GRAY HIGHLIGHTING TO RANGE, BASED ON ENTRY IN ONE CELL
Hello, i am attempting to apply gray shading to the range i12:w12, based on the cell g12. That is, if cell g12 contains the word "yes", i want the cells i12 thru w12 to be highlighted gray. Is there a simple way to accomplish that result? Thanks for any suggestions. ......i.e., is there a good formula to use via the "CONDITIONAL FORMATTING" function. On Aug 26, 11:03=A0am, Dave K <fred.sher...@gmail.com> wrote: > Hello, i am attempting to apply gray shading to the range i12:w12, > based on the cell g12. That is, if cell g12 contains the word "yes"...

Loacate a range of cells
Excel is Company Forms, that have book numbers in seveval range of cells. VB6 we enter the book number which has a var. and a quanity var. So I want to find the book Number in range B30:B65 and put the quanity in range A30:A65. This will be on Sheet1. Any help would be appreciated. Thanks Don Maybe =vlookup() against a table that contains the book number in column A and quantity in column B (probably on a different sheet???) Donald Johnson wrote: > > Excel is Company Forms, that have book numbers in seveval > range of cells. VB6 we enter the book number which has a > var....

Highlight cell 1 and move to last cell
I would like to highlight a cell then jump to last entry to highlight a block so I can copy or delete that block of text. Select all is not what I want. Thanks, Ralphael, the OLD one assume in a single column or row with contiguous data for a column click in the top cell. Hold down the shift key, then hit the end key followed by the down arrow. for a row, it would be the right arrow. -- Regards, Tom Ogilvy "Ralphael1" wrote: > I would like to highlight a cell then jump to last entry to highlight > a block so I can copy or delete that block of text. > Select ...

copying input to another cell
How can I enter text in one cell and have it copy to another cell automatically, not using copy/paste feature. Such as type in date in cell A3 and it automatically appears in cell E33. Also, can this same feature work if I use a data validation list and want that same pull down feature selection to appear in another cell as well. Excel 2003. Seems you could put this formula in E33: =A3 When you change A3 the E33 will change. Do the same with your data valaditation cell where in the destination cell you enter ='whatever the dv cell is' Or you could use this event...

Truncated messages
Hi, I have a NT workstation with MS office 2000 installed on it. Some of the users are experiencing weird problem with that specific machine. some of the messages are being truncated to 2/3 the regular size. Sometimes when they restart the machine, those messages looks fine! Any idea why it happens ?? Thanks, Negar ...

comparing cells in one column to another
I have a column on a spreadsheet and an array of strings. I need to look at the first cell on the spreadsheet and find a matching value in the array of strings. This is how I am doing that With ThisWorkbook.Worksheets(Tracker).Range("Order_Number_Header") For E = 0 To Total_Rows_On_Spreadsheet - 1 For F = 0 To Total_Elements_In_Array - 1 If .Offset(E, 0) = FTS_Order_Number(F) Then msgbox"I Have a Match end if next F next E end with Nothing is matching and I know there are some matches. When I display the value in the fields I see "12345678" for both fields. The f...

Protecting a Cell
Is there a way to protect a cell's formula without protecting the worksheet. I want people to be able to key in data, but not change my formulas. Hi Mark 1. Highlight all cells for which people are allowed to perform entries 2. Goto 'Format - Cells - Protection" and uncheck/disable the protection for these cells 3. Protect the worksheet HTH Frank Mark1 wrote: > Is there a way to protect a cell's formula without > protecting the worksheet. I want people to be able to key > in data, but not change my formulas. ...