Find and Replace 04-13-10

Good day all,
I am trying to change this number 01-465-9273 to show up as 014659273. It is 
easy enough to make the (-) go away but it takes the zeros at the beginning 
of the number away as well which defeats the purpose

Thanks
-- 
Pierre
0
Utf
4/13/2010 1:21:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
690 Views

Similar Articles

[PageSpeed] 23

A couple of alternatives:
- format the cell as Text before removing the dashes
- put an apostrophe before the 0 before removing the dashes
- remove the dashes in another cell, using SUBSTITUTE:
   =SUBSTITUTE(A1,"-","")
- give the cell a custom number format like 000000000

Hope this helps,

Hutch

"Pierre" wrote:

> Good day all,
> I am trying to change this number 01-465-9273 to show up as 014659273. It is 
> easy enough to make the (-) go away but it takes the zeros at the beginning 
> of the number away as well which defeats the purpose
> 
> Thanks
> -- 
> Pierre
0
Utf
4/13/2010 1:56:01 PM
Hi,

The first one will not work

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Tom Hutchins" <TomHutchins@discussions.microsoft.com> wrote in message 
news:B4C1A3AF-77B7-47F9-9089-76358AF0F53E@microsoft.com...
> A couple of alternatives:
> - format the cell as Text before removing the dashes
> - put an apostrophe before the 0 before removing the dashes
> - remove the dashes in another cell, using SUBSTITUTE:
>   =SUBSTITUTE(A1,"-","")
> - give the cell a custom number format like 000000000
>
> Hope this helps,
>
> Hutch
>
> "Pierre" wrote:
>
>> Good day all,
>> I am trying to change this number 01-465-9273 to show up as 014659273. It 
>> is
>> easy enough to make the (-) go away but it takes the zeros at the 
>> beginning
>> of the number away as well which defeats the purpose
>>
>> Thanks
>> -- 
>> Pierre 

0
Ashish
4/13/2010 2:10:57 PM
Thanks Hutch.
First one did not work
Second one, if I understood you correctly would require me to do this 
humpteen times
Third, which seemed complicated to me, therefore I tried it last, turned out 
to be really easy and worked perfectly
Forth and last one, worked for that cell, however when I tried to use that 
cell in another formula, it dropped the zero(s)

Thank you very much
-- 
Pierre


"Tom Hutchins" wrote:

> A couple of alternatives:
> - format the cell as Text before removing the dashes
> - put an apostrophe before the 0 before removing the dashes
> - remove the dashes in another cell, using SUBSTITUTE:
>    =SUBSTITUTE(A1,"-","")
> - give the cell a custom number format like 000000000
> 
> Hope this helps,
> 
> Hutch
> 
> "Pierre" wrote:
> 
> > Good day all,
> > I am trying to change this number 01-465-9273 to show up as 014659273. It is 
> > easy enough to make the (-) go away but it takes the zeros at the beginning 
> > of the number away as well which defeats the purpose
> > 
> > Thanks
> > -- 
> > Pierre
0
Utf
4/13/2010 5:45:02 PM
It worked when I tried it before posting the reply, and it works for me now 
(Excel 2007). In a new workbook, in cells with a default format of General, I 
enter the OP's sample data in two cells. If I just remove the dashes from 
one, it becomes a number and drops the leading zero. If I format the second 
cell as Text, then remove the dashes, it remains as text with the leading 
zero intact. I get the same results with other similar leading-zero strings 
of digits & dashes. 2007 vs. earlier version, maybe?

Thanks,

Hutch

"Ashish Mathur" wrote:

> Hi,
> 
> The first one will not work
> 
> -- 
> Regards,
> 
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
> 
> "Tom Hutchins" <TomHutchins@discussions.microsoft.com> wrote in message 
> news:B4C1A3AF-77B7-47F9-9089-76358AF0F53E@microsoft.com...
> > A couple of alternatives:
> > - format the cell as Text before removing the dashes
> > - put an apostrophe before the 0 before removing the dashes
> > - remove the dashes in another cell, using SUBSTITUTE:
> >   =SUBSTITUTE(A1,"-","")
> > - give the cell a custom number format like 000000000
> >
> > Hope this helps,
> >
> > Hutch
> >
> > "Pierre" wrote:
> >
> >> Good day all,
> >> I am trying to change this number 01-465-9273 to show up as 014659273. It 
> >> is
> >> easy enough to make the (-) go away but it takes the zeros at the 
> >> beginning
> >> of the number away as well which defeats the purpose
> >>
> >> Thanks
> >> -- 
> >> Pierre 
> 
0
Utf
4/13/2010 8:49:01 PM
Reply:

Similar Artilces:

Find and replace 03-04-10
I'm trying to scan a field in one of my tables and find a specific character and remove it. However, the character is a " so I'm having difficulty. The field I speak of of contains the sizes of our material so the values look like this: 1/4" 1/2" 1/3" and so on. How can I find all of the " in my feild and remove them? I don't want to replace them I just want to remove them. Thanks, Chris Savedge Create a query, and in the Criteria row under the problem field, enter: Like "*[""]*" -- Allen Browne - ...

DATE #10
Feb 28,2008 ---> 2008-2-28 Feb 1,2008 ---> 2008-2-1 Format? "����" <cola@hz.cn> wrote in message news:OOQhTbRaKHA.5472@TK2MSFTNGP02.phx.gbl... > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > See your other post "ÎÞÃû" wrote: > Feb 28,2008 ---> 2008-2-28 > Feb 1,2008 ---> 2008-2-1 > > > . > Hi, Highlight the cell, right click in the mouse, cell format, custom format and enter yyyy-mm-dd "ÎÞÃû" wrote: > Fe...

Formula needed 01-22-10
Hi I need a formula that will look in one column range for a certain value and for another value in another column range and when they are both found in the same row, will count the number of occurances. Help!!!! Krissy wrote: > Hi > I need a formula that will look in one column range for a certain value and > for another value in another column range and when they are both found in the > same row, will count the number of occurances. Help!!!! http://www.contextures.com/xlFunctions01.html#SumProduct Omit the "values to be summed". =SUMPRODUCT(--(A2...

Lookup/Find help
Windows XP Professional Office 2000 Hypothetical, but hopefully you'll get the gist of it: I have two worksheets. On worksheet #1, I have two columns. First column is a list of entire workgroup by name and 2nd column is the hours worked. On the second worksheet I simply have an list of names that is a subgroup of those on the first page. These indicate a target group. EXAMPLE Worksheet #1 Sam 35 Joe 37 Mary 20 Beth 41 Ted 38 Worksheet #2 Joe Beth Now, on the first worksheet, I want to add a third column for summing only the target workgroup. Basically, I need a function...

How can I find career change resumes templates?
I like to review some nice "Professional Career Change Resume Templates" for the job market. If someone could tell me where to go on the inter-net, it will be well appreciated. Thanks! You could start at www.google.com "Mike" <Mike@discussions.microsoft.com> wrote in message news:705F99E5-1326-4E81-B98F-A7337C5C9AEA@microsoft.com... :I like to review some nice "Professional Career Change Resume Templates" for : the job market. If someone could tell me where to go on the inter-net, it : will be well appreciated. Thanks! Templates are...

Where can I find a list of known CRM 1.2 issues and workarounds?
I am running a test of CRM 1.2 from MSDN Universal and I am having a number of minor strange issues. Is there a list of known problems, and workarounds posted anywhere? Have any patches been issued or other technical update articles? Is there some sort of monitored support newsgroup or something I should purchase to be able to more effectively evaluate the product? Thanks, Bill Walter This is the only public newsgroup on CRM apart from foreign language versions. For support you would need an agreement with Microsoft MBS. Feel free to ask any technical questions here as we normally can get...

Search and Replace in a column
I can't remember how I did this before and not having much success with my syntax. I just want to do a simple search and replace of all dashes in a field with a blank. Table = Vendor Column = PhoneNumber Basically: replace(PhoneNumber,'-','') I just want to strip the - (dash) from the field. What's the easiest way to do this? SQL Server 2005. Thanks, Greg You posted the correct syntax: SELECT REPLACE(PhoneNumber, '-', '') AS phone FROM Vendor; Or if you need to update: UPDATE Vendor SET PhoneNumber = REPLACE(P...

can't find normal.dot
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC Hello, I need to delete my normal.dot file and I can't find it anywhere. I've had to do this before and I was able to find it in the templates folder, but I guess an update or two has changed that. Can anyone tell me where to find it? Spotlight says that it doesn't exist. But I have to be able to delete it. Is it called something different now? If you're using Spotlight & searching for "Normal.dot" it's no wonder it isn't being found - that isn't the correct name :-) it's ...

Finding maximum value
Hello, I want to find the maximum value in a column, but I want to ignore the negative and positive part of the value. Basically, the maximum difference from zero. Example: the maximum value I am looking for will be -0.467. -0.467 0.345 -0.253 0.411 Thanks Ruan One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN): =INDEX(A1:A4,MATCH(MAX(ABS(A1:A4)),ABS(A1:A4),FALSE)) In article <OGKhnF1KEHA.2100@TK2MSFTNGP10.phx.gbl>, "Ruan" <ruan@aegismed.com> wrote: > Hello, > > I want to find the maximum value in a column, but I want to ignore the > negati...

SendTo PDF Not Working on GP 10
I upgraded a client from GP 8.0 to GP 10.0. Since the ugprade, they are unable to use the SendTo PDF option from within GP. We have tried reinstalling Adobe and that hasn't changed anything. The users can see the PDF being created but a blank email doesn't open from their email client as it used to. The client is on version 10.00.0991 and I thought this issue was to be resolved in 10.0 SP 1 but obviously it hasn't been for us. The client is also using Outlook. Is this still a bug with 10.0 or are we missing something? I am on Adobe Acrobat Standard 8.0 with Microsoft ...

How do I find the inside page in the 3-panel brochure.
I am trying to make a 3-panel brochure and all that seems to be available is the side shown in the brochure options. When I print, it prints both sides yet I can see no way to access the inside part. Thanks. View, status bar... this will allow you to change page views, you will see the page tabs at the bottom of the screen. I am not sure I know what you are asking. Are you saying you are printing the brochure sight unseen? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "springmtn" <springmtn@discussions.microsoft.co...

Lost contact list 04-22-10
I amusing Windows live mail...it had no problem picking up my contacts from Outlook Express. Now that I syncronized messages from my Hotmail e-mail account and my Telus email account into Windows Live, my contact list is only the two contacts from the hotmail account. All the rest are gone. They are still in the Address Book, but I cant access them How can I get it back into Windows Live Mail ??? Help please. You signed into the Hotmail account when you started Windows Live Mail. This is optional and not at all related to getting your mail. When you sign in, WLM sync...

Appointment Activities 12-05-04
Hi, It appears when an appointment passed its scheduled time, it would complete itself without user input, and disappearing from the Home\Past Due Activities view. Other activities, such as phone calls and tasks etc are OK (or it seems). Does this make sense to anyone? If yes is there a solution or workaround? Thank you for your help in advance. Tony Tony, I am getting the exact same behavior, for what unknown reason, I don't know. It's pretty ridiculous if just because the date has passed that an appointment would be autocompleted. Bob "Tony" wrote: > Hi, >...

Find a multiple photos on a page template for Publisher?
Possible to find a template for Publisher to print a page full of multiple photos without recreating the page each time I print. Why not save your page you have created as a template? File, "save as", files of type, scroll down to Publisher template. -- Mary Sauer http://msauer.mvps.org/ "srfaith" <srfaith@discussions.microsoft.com> wrote in message news:157E95AB-1159-4889-B196-61E147E6E240@microsoft.com... > Possible to find a template for Publisher to print a page full of multiple > photos without recreating the page each time I print. ...

Outlook 2003 03-17-10
Dear Sir, 1. Process will complete mailing address to sent item means anything ? 2. Repairmail up screen time that does not match the running because of what ? 3. The move affects the computer to send mail or receive ? Best Regards, On 3/16/2010 8:19 PM, supatsiri wrote: > Dear Sir, > > 1. Process will complete mailing address to sent item means anything ? > 2. Repairmail up screen time that does not match the running because of what ? > 3. The move affects the computer to send mail or receive ? > > Best Regards, What is your native language? Please post ...

Finding Desktop region in MFC
I am working on a commercial application, and recently discovered a bug I have been asked to fix (they threw it back in my lap). Run the application on a 2-monitor system, such as a laptop in a dock with an external monitor. Drag the main window to the second (non-taskbar) window and quit. Now move the laptop to a different dock with the second menu on the other side (or without a second monitor at all) and launch the app. Oops. The app continues to want to go on the side it was last put, even though there is now no monitor there. (Yes, my dock in the office and at home have the second ...

finding calendar and data info
I had to move my primary hard drive into another computer. It is now running as a slavein the new computer. I'm unable however, to find my calendar and contacts from Outlook 2003 when I open the program on the new computer. If I attempt to open the program from the F drive (the drive assigned in the new computer to the hard drive that was moved from another computer) it tells me the program is not installed. If I open Outlook 2003 from the C drive of the new computer, of course, it doesn't find the old calendar and contacts. I would appreciate any and all assistance in findin...

Performance counter for Outlook Finds?
What server performance counters (if any) are there for monitoring the number of "Find" requests that Outlook clients make? -GT ...

Conditional Formatting VBA with formula to find string
Hi, Please can I get some help.... I need to create a conditional format VBA in Excel 2007 (because I have many conditions to include) and I don't know how to do it... The Action Required: If Product A appears in any text string in range($C$95:$C$300) then colour that cell RED, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell BLUE, if Product B appears anywhere in a text string within range($C$95:$C$300) then colour that cell GREEN, and so on through 41 products... Data - I have a list of about 41 Product names in range Z...

Using sdk to find out if an Account already exists
Hi, Want to use sdk to see if an Account already exists , all i have is its name. I've looked at Retrieve method but it requires the Guid. Any equivalent call to Retrieve that just takes name? Or a way to get the Guid from the name? Sample code would be great if available. Thanks for all help. John Hi John, The method you are looking for is RetrieveMultiple as only the guid search gives the certainty to retrieve only one record. All explanations are in the sdk but as the sdk sample is nearly the exact code you want, here it is : // Set up the CRM Service. CrmService service =3D new ...

Doesn't find files w/spaces in name
There are a lot of folders and Excel spreadsheets on our shared drive that I need to open. Most of the folders and file names are named like: get reports\reports sep 2004.xls When I try to open one, Excel doesn't find it and I think it's because there are spaces in the folder names and/or file names. Is there a setting in Excel that will help me get around this? This was working until I had a glitch in Excel and the technician fixed the glitch and this problem appeared. Any help would be appreciated. I found an answer and so far it works. "C:/program files/microsoft off...

SQL statement to find a particular column within all tables
I am looking for a query that will allow me to find all instances of a particular column within all tables so I know where they are all located. Does anyone have such a query they would be willing to share? Thank you. Pam, I posted this query a few months aback on my blog (http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html), but here is the excerpt: select distinct rtrim(objs.name) from syscolumns cols inner join sysobjects objs on (cols.id = objs.id) inner join sysindexes indx on (cols.id = indx.id) where (cols.name = 'ACTINDX') and (ob...

Find number of weekdays and wekend days given a total number of da
Hello, I have a question for the gurus here. I am working on a spreadsheet where in column A I have Total # of Vacation days. Example: # Vac Days --- # Weekdays --- # Weekend days 75 55 20 44 32 12 25 19 6 I figured this out by simply creating a list like this 1 Weekday 2 Weekday 3 Weekday 4 Weekday 5 Weekday 6 Weekend 7 Weekend And so on... And then doing a countif to figure out how many of either on...

"Replace" keyboard shortcut
MS Word 2004 Re: Find/Replace, it is a quick, efficient keyboard process after highlighting a string to be copied to hold the Command key while pressing C, F then V. When I then want to enter the "replace" string, my hands have to leave the keyboard, go to the mouse and click the Replace tab before I can continue. I cannot find any reference that offers a keyboard short to drop down the Replace window. Is there one? Thank you. I'm not sure if there is a direct keyboard shortcut for the Replace command, but once you've brought up the Find dialogue, you can simpl...

disappearing e-mail 04-21-10
This has happenend several times: Vista; When I open my e-mail, some email just disappears, it's not in any folder, it's just gone. I''ve read other threads that say make sure you are are set to show all messages and I am. What next? "Trish1856" <pmf1856@cox.net> wrote in message news:C8EFF7C5-B8C5-4F52-8D9F-070299902F6C@microsoft.com... > This has happenend several times: Vista; When I open my e-mail, some > email > just disappears, it's not in any folder, it's just gone. I''ve read other > threads that say make...