How to delete characters after a value in a cell?

I've got a list of url's in excel that I need to delete everything
after the .com could someone tell me how to do this? I'm running excel
2007
0
7/25/2009 3:27:42 AM
excel 39879 articles. 2 followers. Follow

4 Replies
511 Views

Similar Articles

[PageSpeed] 3

One way, in say B1, copied down: =LEFT(A1,SEARCH(".com",A1)-1)
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
--- 
"mkabwilliams" <mkabwilliams@gmail.com> wrote in message 
news:bbcc9a6b-8ac3-4848-b389-e66ab6d8a117@x5g2000prf.googlegroups.com...
> I've got a list of url's in excel that I need to delete everything
> after the .com could someone tell me how to do this? I'm running excel
> 2007 


0
demechanik (4694)
7/25/2009 3:47:58 AM
Max's method will return everything upto the .com, e.g. www.someplace.com.au 
becomes www.someplace

=LEFT(A1,SEARCH(".com",A1)+3) will return including the .com, such that 
www.someplace.com.au becomes www.someplace.com

The reason being, search() returns the starting character of the search 
string.

HTH
-- 
Cheers,
RyanR

--- pls click yes if I''ve helped you ---


"Max" wrote:

> One way, in say B1, copied down: =LEFT(A1,SEARCH(".com",A1)-1)
> -- 
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:27,000 Files:200 Subscribers:70
> xdemechanik
> --- 
> "mkabwilliams" <mkabwilliams@gmail.com> wrote in message 
> news:bbcc9a6b-8ac3-4848-b389-e66ab6d8a117@x5g2000prf.googlegroups.com...
> > I've got a list of url's in excel that I need to delete everything
> > after the .com could someone tell me how to do this? I'm running excel
> > 2007 
> 
> 
> 
0
RyanR (3)
7/25/2009 11:39:01 AM
Another way:

Select the range to fix
Edit|replace
what:  .com*  (the asterisk is a wild card)
with:  .com   (or .COM or any upper/lower case you want)
replace all



mkabwilliams wrote:
> 
> I've got a list of url's in excel that I need to delete everything
> after the .com could someone tell me how to do this? I'm running excel
> 2007

-- 

Dave Peterson
0
petersod (12005)
7/25/2009 12:01:00 PM
Agreed, my error. Thanks for correction.
-- 
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
--- 
"RyanR" <RyanR@discussions.microsoft.com> wrote in message 
news:F80E2E3C-A69B-4DF2-B6AE-61AEF055130C@microsoft.com...
> Max's method will return everything upto the .com, e.g. 
> www.someplace.com.au
> becomes www.someplace
>
> =LEFT(A1,SEARCH(".com",A1)+3) will return including the .com, such that
> www.someplace.com.au becomes www.someplace.com
>
> The reason being, search() returns the starting character of the search
> string.


0
demechanik (4694)
7/25/2009 12:11:13 PM
Reply:

Similar Artilces:

Cell contents not appearing in title bar
When I highlight a cell the contents don't appear in the line at the top so that I can edit them Hi maybe the sheet is protected? >-----Original Message----- >When I highlight a cell the contents don't appear in the line at the top so >that I can edit them >. > The cell contents are not supposed to appear in the Title bar. They appear in the Formula bar. To display the formula bar, click on View | Formula Bar. "petersmyth1" wrote: > When I highlight a cell the contents don't appear in the line at the top so > that I can edit them If your...

Cell containing formula won't resize culumn width
In a column for which I have turned on the autofit column width, I have a series of numbers with a sum formula at the bottom of the series. The column width will automatically resize for numbers entered that are wider than the current width; however, if the result of the sum formula is wider than the current width, the column will not automatically resize to accomodate the width of the resulting sum. Is there a way to fix this without manually having to resize the column? The worksheet is protected, so it is a pain to unprotect, change and reprotect. You could have a macro do it for you with ...

How do I delete emails from profile w/o deleting from mainserver?
I have a profile for another co-worker set up on my outlook. She doesn't delete her emails. I would like to know if I delete them on my account, will they be deleted on the POPserver as well, and what the consequences of deleting the emails will have on my profile, i.e., will the ones I deleted try to download again when I log in? Help. Thanks. ...

Messages saved to Deleted Items instead of Drafts
I am using Outlook 2003 SP2 (Office 2003 Pro) in Windows XP Pro SP2, with all available patches to Windows and Office. If I start a message or a Reply and click the Save icon, the saved message is "saved" to the Deleted Items folder instead of the Drafts folder. If I close the message (as one would do if one were planning to continue later) and empty the Deleted Items folder the message is lost. If I go to Tools, Options, Preferences, E-mail Options..., Advanced E-mail Options..., "Save unsent items in:" has "Drafts" selected, so it should save them in the...

Merge/connect cells without loss of data?
Hi everyone, stupid question I did not find an answer for even after a long search: I have an Excel table with cells which contain fax numbers. One cell i a row contains the dialling code, the next cell in this row contains th fax number. For a series-letter I created with Word, these two cells have to b merged into ONE, so Word can read the fax number including the diallin code in "one piece". So, for example, I merge A1 ("040") with B1 ("636465"), the resul should be A1 ("040636465"). But the standard procedure to merge/connec cells in Excel only ...

SUBTRACTING TIME VALUES INSTEAD OF DECIMAL!!
Please help! I have used the 00:\00:\00 format to input times without having to press the colon every time. Now though, when Excel tries to minus one time from another, because of the custom format it only recognizes times as numbers... e.g. 12:15:30 - 12:14:50 Excel thinks this equals 00:00:80!! Can anybody help? How do I get it to recognise that the answer is 00:01:20 ? Thank you See previous post .misc -- HTH RP (remove nothere from the email address if mailing direct) "Lexicon" <Lexicon@discussions.microsoft.com> wrote in message news:5D1A072F-2DD2-49CA-8569...

I accidiently deleted a 12 page workbook, how do I get it back?
I was copying a worksheet to start a new, 12 month, 12 sheets, new workbook and when I copied the page to start the new worksheet, the sheet I pasted onto new doc, was okay but I have to adjust all the margins, etc. However, when I went back to the 2009 doc, I clicked on the page to release it from being copied again, and the whole 2009 - 12 worksheets - where gone. Help me PLEASE, I need the 2009 document.....Thanks!!!!! Hi, List of options:- 1. use your backup. You do have one don't you!! 2. Close without saving. You'll lose the work you've done but should ge...

Simple Delete Query
I have a table that includes the field titled "State". I want to delete all records in a table except records that have "AZ", "CT" or "NY" in the state field. I have tried the criteria of =Not "AZ" or"CT or "NY", but I'm not getting the correct results. I only retain AZ records. Any help you can provide is appreciated, thank you. "SITCFanTN" <SITCFanTN@discussions.microsoft.com> wrote in message news:845CAAD2-8B83-4335-9CB4-A29A9E9D6F37@microsoft.com... >I have a table that includes the fiel...

counting cells that contain a specific value
I have a spreadsheet containing hundreds of business contact names, addresses, etc., with the city and state in adjacent columns. I'm trying to find a good way to easily and quickly count the number of contacts in a particular city. One obvious way might be to sort by city name and count the rows, being careful not to count those contacts in a city with the same name, but a different state -- e.g., Columbia MD vs. Columbia SC. There are a lots of entries for each cities, so a manual count could easily be wrong. "Countif" looks like it could be used to do this if I set u...

Highlight 30th characther in a each cell in a column
Im working on a spreadsheet and in one column I need to highlight the 30th character of each cell, is there any way this can be done. HELP ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Not sure what you mean by "highlight". You can only apply character formatting to constants, not the results of formulas. This changes the color of the 30th character of all constants in column C to red: Public Sub ChangeColorOf30thCharacter() Dim cell As Ran...

Changing a text name within a cell based on file name
Hello, I wonder if it is possible to change a cell contents based on the file name. For example I am using Excel file 2003 in my workbook sheet 1 in cell A2 it is populated with a text field - company name "Zenith Manufacturer" and in cell A3 has the company location text field "Tampa Florida" I have about 50 reports. The file name on one report can be Sch88 (Zen) or Ex 1 (Zen), etc. All of the 50 reports have unique names, but has the same identifier within the parentheses (Zen). Here is my problem I have to duplicate the same 50 reports with a different compan...

Project invoices can be deleted
When I create a billing entry in project, it automatically assigns it the next transaction number. I'll save it to a batch so that I can preview/print the individual invoice. If I Delete the invoice (after it has been saved), rather than post it, I cannot find a record of it anywhere. Further, the next invoice created will use the next sequential number, leaving a gap in the invoice numbers. I find this to be a poor control issue. GP should replace the Delete button with a Void button, thus leaving a nice audit trail and no missing invoices. ---------------- This post is a sugg...

Printing problem with amalgamated cells
Hi, When I print a form I created, some characters are missing in merged written cells. When I uncheck this option it prints correctly. But, it is better when I write in a table's location to have the whole package of cells united. Any ideas to fix that? Thx! Werner -- Werner ------------------------------------------------------------------------ Werner's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24304 View this thread: http://www.excelforum.com/showthread.php?threadid=384646 If I can assume that "amalgamated" is your language's w...

How do I delete or hide folders in my Outlook folder list?
I just upgraded from Microsoft Office 2000 Professional to 2007 Professional. I don't use all the bells and whistles provided by Outlook Express. I want to clean up my folder list by hiding many of them. When I go through some of the drop-down menus, I see clicks for deleting some of the folders, but they are "grayed" and will not respond to a click. Can anyone help me? Joan 72438 wrote: > I just upgraded from Microsoft Office 2000 Professional to 2007 Professional. > I don't use all the bells and whistles provided by Outlook Express. I want > to clean u...

How can I attach a hyperlinked object in a workbook cell to email
I've very limited knowledge regarding programming in VBA, and would like to create a control button whereby hyperlinked objects within excel cells can be selected and sent as attachments to predetermined recipients for action automatically, and get a response back when that action has been completed. the idea being to have the same function would be available available on every row in the shhet of the workbook as required, such that the control button could be copied into each row as required. Any assistance would be greatly appreciated. ...

In Excel, using line wrap with text, some cells will appear as ##.
In Excel, using line wrap for text, some cells will appear as ######### on one line. Attempts to correct the problem by re-entering the format (text-line wrap checked-top alignment) do not work. Even copying the style from a "good" wrapped cell does not correct the problem. If it has to do with column width, double-click on the line between the column with the ### and the one to right, you 'll expand the column, exposing the full number. EX: cell A1 has ####, so position the cursor between the A and the B column headers, on the line until the cursor turns into a black lin...

How do I stop cells from expanding?
Hey all. Just got Excel 2003, and have a real basic question. I'm working in large files with very long cells, and everytime i click into one to edit it, it expands to take up almost the entire screen. Problem is, i need to look at cells in other columns, and this is a huge pain. How do i get it to stop? Thanks in advance! If it is the formula bar that bothers you can turn it off temporarily under view>formula bar -- Regards, Peo Sjoblom "frodomojo" <frodomojo@discussions.microsoft.com> wrote in message news:FD91C628-9456-454D-9E2A-EF42F6F7F494@microsoft.co...

My inbox messages automatically delete when I close outlook. Why?
Lately when I close outlook all my messages are automatically deleted. I cannot see that they have been moved. And they do not go to the deleted folder. They just seem to disappear. Is there a setting that may have accidentally been set to make this happen? Are you using a Hide read view? look on the View menu to select a different view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-reque...

Outlook 2007: Ctrl+Backspace spits out squares rather than deletes words
I noticed this in the beta release but shrugged it off as a beta bug. I see now that I should have reported it as a bug because it seems to have been rolled out in the RTM release. I have gotten into the habit of hitting Ctrl+Backspace to undo entire words of recently typed text. This is a great time saver. Unfortunately, in Outlook 2007, I am seeing the e-mail editor spit out squares (i.e. valueless Unicode character blocks). It's erratic; I can hold Ctrl down and keep hitting Backspace and it might create two squares, maybe five squares, maybe one, before it will finally "ca...

how to get an value from a different book
hi i have to .xls files - and i need to get an number from another workbook. My book nr 1 have this A1: (Itemnumberr) B1...G1 (some text). H1 (item weight) Booknr 2; A1 (itemnumber)....B1....K1 (some text) L1 (item weight) What funktion is my best solution to get the item weight from book 2 into book 1 -- Mortenn First go to book1 and copy the cell in question. Then go to book and Paste Special with the link check box checked. -- Gary''s Student "Mortenn" wrote: > hi > > i have to .xls files - and i need to get an number from another workbook. > >...

Deletion of client-only rule
Recently upgraded from 2000 to 2003 and now have some client-only rule I wish to delete - BUT HOW - they are not visible when I am off-line ('cause they are stored on the server) but when i try to select while online it is automatically unselected and so i cannot change or delete !!!! When i upgraded I did agree that the rules should become client-only but now I cannot even access them. Try starting Outlook with the "/cleanrules" or "/cleanclientrules"command line switches: http://support.microsoft.com/kb/826964 Rules That You Created in Outlook 2002 Are Not Visible ...

Excluding Alpha character in text field by filtering.
I have a query based on a table./ I have one numeric field that has alphanumber entries: A2000-000; 101010010,A2004, etc.. I what the filter to exclude all records that have an apph beginning - ie. in eg. above exclude: A2000-000,A2004. How do I do this? Your assistance is greatly appreciated. You could use WHERE Left$(AlphanumericField, 1) NOT BETWEEN "A" AND "Z" or WHERE Val(AlphanumeriField) <> 0 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Grace" <fhsmith7@bellsouth.net> wrote in message news...

Making a cell only ecept an X and protecting code
OK here what I got I need F22 and F23 to only except an X. At this point it only triggers the red if you enter an X but the cells does let you type in anything and that will be a problem. Then Id like to protect or hide the code some how so people don't mess with it. So if you have a minute or two I would be most great full. Private Sub Worksheet_Change(ByVal Target As Excel.Range) Rem Put an X in cells F22 or F23 makes Cells F14 AND F16 red Rem _____________________AREA WORKS_____________________ Rem YOU CAN ENTER ANY TEXT, BUT ONLY AN X TRIGGERS THE RED If Target.Address = ...

Deletion of templates
How do I delete unneeded templates that I created. go to the folder where you stored them and delete them -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Oxbow" <Oxbow@discussions.microsoft.com> wrote in message news:81D974D3-BB4D-4F6B-B968-E7BED19A662D@microsoft.com... > How do I delete unneeded templates that I created. The templates are in a folder (hidden) similar to this: C:\Documents and Settings\<<user>>\Application Data\Microsoft\Templates -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msn...

Deleting Contact and Calendar folders
I have imported old .pst files, and now have contact and calendar folders that I would like to delete. Some allowed deletion, but others are grayed out. I am signed on as administrator. How can I get rid of these? Also, how can I import a .ost Outlook file? -- SRJ Strange; your other post states that you lost your outlook files "SRJ" <SRJ@discussions.microsoft.com> wrote in message news:D20546D7-3463-4046-B482-89C23D463089@microsoft.com... >I have imported old .pst files, and now have contact and calendar folders > that I would like to delete. Some ...