Deleting Parts of Cells

I have a list of information in a column.  All the information has the format 
of having numbers and letters then a / and more data.  I only want to look at 
the data to the left of the /.  So, I would like the data to the right of the 
/ deleted, including the /.     For example, I need YYY9/5 to read YYY9.  Any 
suggestions?
0
GOL (6)
11/3/2005 3:09:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
686 Views

Similar Articles

[PageSpeed] 21

You could bring it into Excel as a txt file 
open the txt file in excel
Text Import Wizard will appear
select  >delimited
check > other and type the "/" into the box
finish
you should have a separation where the   /  was.





"GOL" wrote:

> I have a list of information in a column.  All the information has the format 
> of having numbers and letters then a / and more data.  I only want to look at 
> the data to the left of the /.  So, I would like the data to the right of the 
> / deleted, including the /.     For example, I need YYY9/5 to read YYY9.  Any 
> suggestions?
0
ufopilot (81)
11/3/2005 3:20:04 PM
Hi GOL.  Assuming your list starts in A2, you could enter the following 
formula in B2 and copy down to match your list in Column A.  When finished, 
you might want to copy Column B and paste special>values over itself:  
=LEFT(A2,SEARCH("/",A2,1)-1)  HTH
-- 
Sincerely, Michael Colvin


"GOL" wrote:

> I have a list of information in a column.  All the information has the format 
> of having numbers and letters then a / and more data.  I only want to look at 
> the data to the left of the /.  So, I would like the data to the right of the 
> / deleted, including the /.     For example, I need YYY9/5 to read YYY9.  Any 
> suggestions?
0
Michael5492 (655)
11/3/2005 9:15:02 PM
Reply:

Similar Artilces:

DoNot Print selected cell
I used conditional formating to not print an area formula is =$B$9:$C$9="1" also used =$B$9:$C$9="x" font and pattern formats are set to white. Followed these guides: http://www.rondebruin.nl/print.htm http://www.contextures.com/xlCondFormat03.html#Print I do not want to print cells B9 and C9 Slim Hi what problem did you exactly have following the instructions on both sites? -- Regards Frank Kabel Frankfurt, Germany SlimPickins wrote: > I used conditional formating to not print an area > formula is =$B$9:$C$9="1" > also used =$B$9:$C$9="x&quo...

Cell wrap ceases after several lines?
Any ideas why a combined cell crossing several columns should wrap text fine for several lines, and then go off the side for the rest? The text is all wrapped when actually editing within the 'cell', but when one leaves it only the top few lines are wrapped and readable. Cheers, S You'll see this if the cell contains a longggggg string of text. But you if you add alt-enters (to force new lines within the cell) every 80-100 characters, you'll be able to see more. spamlet wrote: > > Any ideas why a combined cell crossing several columns should wrap text fine &g...

Applying formula's to many cells
I am trying to multiply many cells by 1000, it will take years to type "*1000" into every cell. Is there any quick method of applying this sum to all cells? Cheers Luke Hi Luke yes, type 1000 into an unused cell somewhere and then copy it, select the cells that you want to multiply by 1000 and then choose edit / paste special, multiply (please, test first on a backup of your data to see if this is what you're really after) Cheers JulieD "Luke" <Luke@discussions.microsoft.com> wrote in message news:29241BEB-606C-4551-9088-15C02E9AF7A7@microsoft.com... > ...

Problem trying to move between cells on a protected worksheet.
I am using the TAB key to move between the unlocked cells on a protected worksheet. For some reason, when I get to the end of some rows, the cursor returns to the home cell on that row, not to the next row down. Is this because the cells are merged...if so, how can it be corrected? thanks ...

split combined Time Date cells
from file dump have combined date time cells eg "14/04/03 14:20" (value 37725.59722). Want to perform time analysis so need to split to 2 separate cells; eg. "14/04/2003" (value 37725) & "14:20" (value 0.59722). Is there a fnc to do this? (Currently convert cell to value, then fncs trunc & cell less int(cell) then refmt to date dd/mm/yy & time hh:mm respectively) Hi Mark, With your date/time in A1, B1= Int(A1) Format as date C1=A1-Int(A1) Format as time Drag the formulae down as far as required. --- Regards, Norma...

how to program a cell ?
I want to program cell A1 with a symbol + and B1 with a symbol - When I click on A1, I want A2 to increase by 5 min. When I click on B1, I want A2 to decrease by 5 min. Can someone help me ? Thanks ! If possible, can you email the EXCEL file. -- acwai Why not add a Spinner from the Forms Toolbar? Draw the Spinner over B1 and B2 or near enough. Set it to increment by 5 when clicked up or down. Gord On Thu, 29 Mar 2012 15:59:25 +0000, acwai <acwai.9ba7228@excelbanter.com> wrote: > >I want to program cell A1 with a symbol + and B1 with a symbol - > >When I click ...

HLookup, multiple tables and range names in other cells
Hello, I am beginning to discover Excel with the aid of various textbooks but I am stumped on one problem and don't wish to embark on VBA custom functions unless I have to. I am intending to use HLookup with multiple tables (over 15) in the same worksheet. So here is my problem, =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) The 'table_array' part can be a range or the name of a range, if I have a cell that is calculated to produce the name of a range eg. an INDEX and MATCH formula, how can I get 'table_array' to accept this without errors? eg.=HLOOKUP(A...

Deleting rows
I have a question, I have a spreadsheet which contains two columns of text which are b and c and 7 columns of numbers and formulas which are d-j, there are some rows on which I will have nothing but zeros and blank cells, I would like to make a macro which would delete all the rows if there are any empty/zeros within columns d-j. Thank you. Joe Diaz Wrote Clarification please. Do you want to delete the row even if there is only one empty/zero cell in the row... or do you want to delete the row only when all seven cells for the row in D through J are empty/zero? -- Rick (M...

Deleting or Adding Zeros
I have two lists in CSV format that I need to join tables in Access. One list, which is too large to open in Excel has no zeros at the end or a letter. The other list has zeros or a letter. How can I add zeros to one list or delete zeros from the other list? 100-01-001P 100-01-001R 100-01-001S 100-01-002 (The other list has this number: 100-01-0020) 100-01-003A 100-01-004 (The other list has this number: 100-01-0040) 100-01-005 (Etc.....) 100-01-008 100-01-009 100-01-010 100-01-020E 100-01-020F 100-01-020G 100-01-020H 100-01-021 For this example, I'm going to assume that you wan...

Backspace randomly starts deleting bullets...
I write a lot of lecture outlines in MS Word using bullets. It works extremely well, however, the funniest thing happens and I'd love to be able to stop it. Everything will work great for a time: When I press tab, the bullet will indent one tab forward. When I press backspace, the bullet will de-indent one tab backward. .... then, all of the sudden, without any purposeful doing on my part: When I press tab, the bullet will indent one tab forward. However, when I press backspace, it deletes the bullet. .... and this continues for the rest of the document and I can't...

Emails automatically deleting after opened and read
Can someone give me a little insight on why this is happening? I have a receptionists who reads her emails and then they automatically move to the deleted items. There is not a rule set up in the wizard -- what gives??? Thank you for you any information you may have. DP ...

Validating data in a cell with a formula
Hi, I am trying to use Data Validation in a cell which contains a formula but it doesn't work. If I put a Validation test (say cell A1 must be greater than 0) and set the validation alerts on, and then try and type a negative number into A1 the validation works fine. IF however I put a formula in A1 like = B4 and then type -5 in B4, cell A1 then shows -5 and no validation alert is triggered. Does validation only work for typed in figures or can it evaluate the result returned to a cell by a formula?? Help much appreciated and thanks in advance Pach Validation does not work with formu...

Function for determining if cell content is formula vs value
Is there a way to determine if a cell contains a formula or a value? The ISxxx( ) and CELL( ) functions don't appear to be able to do this. I want to use conditional formatting to show if a cell with a formula has been changed to a value and so far I'm completely stumped. Oscar Hi Oscar, You would need to install a UDF. Function HasFormula(cell) HasFormula = cell.HasFormula End Function =HasFormula(B2) =personal.xls!HasFormula(B2) to install and use a User Defined Function (UDF) see tp://www.mvps.org/dmcritchie/excel/getstarted.htm --- HTH, David McRitchie, Microsoft MVP -...

Making items inactive as opposed to deleting them
I have read that it's bad to delete items, and I think I found that true because I went back recently to look at a prior sale and it wouldn't pull up any info on it cause I had deleted the item in question. So, I went in and made a few items inactive to try it, and now I can't find them. Sorry for being an idiot, but how do I access the inactive items in inventory? Brad Couple things you can do is run an item quantity report and filter for inactive = yes....you can also run an inventorry wizard task "set items active" it will show you the items that are flagged...

email rule to permanently delete unread messages
Due to some wacky things going on with my spam software, exchange and my backup software, I'm interested in creating a rule that will permanently delete any UNREAD message that I move to the deleted folder. I didn't see anything in the rules wizard that allows me to do that exact thing. Thanks! Sue ...

can't delete messages #2
I can't delete any messages in the outbox. please help ...

Cannot delete empty folders, says Winlogon.exe is using them
I have a couple of folders under the "Program Files" folder which are completely empty, scanned them, and no files left in them. However, when I go to delete these two directories, it says other programs are using them. I have a utility called Unlocker which I could use to break these locks, and it's the util that tells me exactly which program is using these folders, but I am hesitant to delete something that winlogon in using. The two subfolders that I'm trying to remove are "microsoft frontpage" and "xerox", neither of which are program...

Editing the contents of a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel In previous editions of Excel the cell editing was different. Say I were working on monthly expenses and a cell represented that month's accumulated costs for groceries. As the expenditures became known you would simply enter that cell and in the fx window that appeared just below the header section, the cell contents would display. You could add a (+) after the last value and then your next item of expense. OR you could use an F key to bring up the same editing window. On the 2008 program I have a floating ...

fill all row cells with double click
hi, sometimes i have problems filling completely a row with a double click, because sometimes i double click and it doesnt fill all row cells, only fills two cells, or i must drag until the end to fill completely the row. do u know why? is there a solution? thanks Happens when the adjacent col contains blank cells The copy/filldown action will stop at the 1st blank cell To copy down large ranges where the adjacent col may not be fully populated, you could do it like this: Click inside the namebox*, type the range, eg: C2:C8000 Press ENTER, to select the range *the box with the dropdo...

Integration Manager to delete Sales Orders
I have a customer who will be entering orders all month. At the end of the month they want to combine these orders to make one invoice for the customer. I have created an integration that will export all the open orders and then integrate them back in as one invoice per customer. My question is can I in Integration Manager use that same exported file to delete or void the original orders. Thank you,Amy Someone may have a more eloquent solution, but I recommend creating a macro which voids the transactions and use that file as the source. Do you have an example on how to create a ma...

javascript BUG: delete before for in statements breakes prototype.
I wrote a piece of code that shows the problem. When you delete a member of an object with a delete operator, you should be able to see the prototype's value of the same property name if it exists (because of chaining). This works fine unless you try to do a for in statement of the object after the delete, in which case the value of the prototype is not returned, in fact it's never retrieved at all. The example has two instances of Bar, both of them should have 3 members before and after the delete, but after the delete only 2 members are found on one of the instance...

How can I highlight a cell or row or column in excell 2007?
I am using excell 2007 beta and wanted to highlight a row so I can organize the group/s in each different colour, but I could not find the highlight. How can I find the highlight, please? The cell and text colour dialogs look the same, and can be found on the Home tab, and the Font group. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "privateicon" <privateicon@discussions.microsoft.com> wrote in message news:EE787B8F-7299-4263-BD6B-D41ED112C6C9@microsoft.com... >I am using excell 2007 beta and wanted to highlight a row so I can organize > the grou...

Delete all rows where there is no entry in Column 'C'
Dear Experts: I got a multi-column, mulit-row table with numerous data records. Some records do not have any entry in Column 'C'. I now would like to be able via a macro to delete all records that do not have any entry in Column C. How is this achieved? Thank you very much in advance for your great help. Regards, Andreas Public Sub DeleteRowOnCell() Set coltocheck =3D Columns(3) 'if you want user-selectable column use inputbox 'Set coltocheck =3D Application.InputBox(prompt:=3D _ ' "Select A Column", Type:=3D8) ...

Linked cells between worksheets and sorting
Hello, I have 500+ rows of data on a worksheet called 'Personal Details'. From this worksheet, two columns (first name and surname) feed into other worksheets by cell linking e.g. into the 'Course Information' worksheet (links are all within same workbook). The other worksheets have additional columns of data e.g. 'Course Information' has a column called Tutor, the tutors names are linked back into the 'Personal Details' worksheet. The problem is for example, when I sort the Surname column on 'Personal Details' A-Z, the tutor name column...

Trying to validate input based upon values in two other cells
I'm trying to pop a warning message (Datavalidiation Stop) or display a text value in another cell when two conditions exist: Cell D4 is = 0 and cell G4 is having a numberic value entered into it. If Cell D4 is > 0 then any number can be entered into G4. Data Validation>Allow>Custom =AND(D4>0,ISNUMBER(G4)) Gord Dibben MS Excel MVP On Thu, 10 Dec 2009 13:23:01 -0800, JG <JG@discussions.microsoft.com> wrote: >I'm trying to pop a warning message (Datavalidiation Stop) or display a text >value in another cell when two conditions exist:...