Trim, clean & proper in 1 command ?

I would like to perform a Trim, Clean and proper in cell B1 all in 1
command.
Anybody know the proper formula?
Or should it be done with a macro?
Thanks



0
8/6/2004 1:51:38 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
512 Views

Similar Articles

[PageSpeed] 27

Hi
=PROPER(TRIM(CLEAN(A1)))

>-----Original Message-----
>I would like to perform a Trim, Clean and proper in cell 
B1 all in 1
>command.
>Anybody know the proper formula?
>Or should it be done with a macro?
>Thanks
>
>
>
>.
>
0
frank.kabel (11126)
8/6/2004 2:03:23 PM
I'm assuming (perhaps incorrectly) that you're not familiar with the concept
of nesting functions within functions. Crank up Excel's help thing, and in
the answer wizard, type "nesting functions". That should help. It does mean
*all* nested commands will work, but this will get you started.

"Larry" <notHere@aol.com> wrote in message
news:K1MQc.255043$XM6.104861@attbi_s53...
> I would like to perform a Trim, Clean and proper in cell B1 all in 1
> command.
> Anybody know the proper formula?
> Or should it be done with a macro?
> Thanks
>
>
>


0
8/6/2004 2:04:26 PM
Hi Larry,
I would suggest using a macro,   And the CLEAN Worksheet Function
is not likely to you any good.    See and read more at
   http://www.mvps.org/dmcritchie/excel/join.htm#triall
and the   Proper_Case macro in
   http://www.mvps.org/dmcritchie/excel/proper.htm#proper
You can combine the two in your macro.
---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

"Larry" <notHere@aol.com> wrote in message news:K1MQc.255043$XM6.104861@attbi_s53...
> I would like to perform a Trim, Clean and proper in cell B1 all in 1
> command.
> Anybody know the proper formula?
> Or should it be done with a macro?
> Thanks
>
>
>


0
dmcritchie (2586)
8/7/2004 2:24:08 AM
Reply:

Similar Artilces:

5.7.1
'support@insight.com' on 1/12/2005 10:28 AM You do not have permission to send to this recipient. For assistance, contact your system administrator. <*.com #5.7.1 smtp;550 5.7.1 <support@insight.com>... Access denied> i seem to be able to send to some domains but not to others i am also getting non-delivery reports of 5.4.0 and 5.1.8 Ask the administrator of that site whats going on or google the RBL websites and lookup whether your domain has been blacklisted. On Wed, 12 Jan 2005 10:29:05 -0800, "Derek B. Moore [MCP]" <DerekBMoore...

can't get "sum" to add up properly
Hi, I'd appreciate any help with my problem. I have the following A B C 1 Benefit Coverage Amount Monthly Premium 2 Employee Life .21/$1,000 $67,000.00 $14.07 3 Dependent Life (if applicable) family $2.00 4 Employee AD&D .04/$1,000$67,000.00 $2.68 5 LTD 2.58/100 $1,529.00 $39.45 6 Dental family 47.86 7 Health family 48.90 8 EAP (if applicable) yes $2.20 9 ...

Free/Busy information only displayed from 1/1/05 to 3/1/05
Since the beginning of the year, the Free/Busy window for almost ALL of my (800+) users is 'stuck' at 1/1/05 through 3/1/05. Most users publish 2 month's worth of free/busy from Outlook, so I should see one month prior to today's date and two months ahead. If a user increases the # of months published in outlook, the free/busy time is adjusted to 1/1/05 through the correct month, so I know that _some_ level of communication is happening between outlook and exchange. I run a mixed exch5.5 and exch2003 environment. Most clients are outlook 2000 or 2003. The problem oc...

Does rms 1.3 support pindpad 1000se
Does rms 1.3 support pindpad 1000se. We have a pindpad 1000se, can we use the driver on the rms, do we have to install the driver for the pinpad in the cd? Yes it will work and you can use the drivers provided my Microsoft. "TJW" <TJW@discussions.microsoft.com> wrote in message news:9146817C-4E6E-4584-8AEA-DDC12D99BB44@microsoft.com... > Does rms 1.3 support pindpad 1000se. We have a pindpad 1000se, can we use > the driver on the rms, do we have to install the driver for the pinpad in > the > cd? but the one i have is a usb interface, the opos driver fro...

Will my Office OS X updated to 10.1.9 run on my new Intel nased Mac
Version: v.X Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I have Office for Mac OS X, uppdated to 10.1.9., on my old 10.3.9 G4. I am taking delivery of an Intel based Macbook Pro running 10.5 Will my old Office run on Leopard? If not, is the Home/Student Version equivalent, or do I have to buy the much more expensive Office 2008? Can you get rebates? You've actually raised several issues here, so see the in-line responses: On 6/28/09 12:27 PM, in article 59b773f8.-1@webcrossing.caR9absDaxw, "Sparkswj72@aol.com@officeformac.com" <Sparkswj72@aol.com@officeform...

Converting Leads to Opportunities in CRM 3.0 (changes since CRM 1.2?)
We are in the process of upgrading from CRM 1.2 to CRM 3.0. In 1.2 we were able to convert Leads to only Opportunities. However, in 3.0 it appears that you must also convert the Lead to an Account or Contact as well (i.e. after clicking on the Convert Lead button, if you select only the Opportunity checkbox, the OK button is disabled, however, if you also select either Account or Contact, the OK button becomes enabled). Is this a change in functionality from CRM 1.2 or is there a setting that impacts this behavior? Thanks in advance. ...

1 domain and 2 email servers
Hi All, There are 2 domains: domain1.com domain2.com Each domain has there own email server. Now we purchase a 3rd domain: domain3.com. I need to setup email address for both companies to have a @domain3.com address. I can get this work fine with one mail server as I have the MX record pointing to it but how do I get it to work for both mail servers. Thanks. JN You really should consolidate Exchange but in the meantime: http://support.microsoft.com/kb/321721/en-us Nue "JN" <jnsimex@rogers.com> wrote in message news:elEYkbEVGHA.5828@TK2MSFTNGP10.phx.gbl... > Hi ...

CRM 1.2: Access denied on viewig Accounts and Contacts
I recently had to rebuild my CRM 1.2 database. Since then, users get an 'Access denied' message when viewing the details of accounts and contacts. It seems that teh security descriptors are invalid. My questions are: 1. I intend upgrading to CRM3.0 anyway - is this going to rebuild security that will bypass this error or am I just importing incorrect parameters as it is? 2. Changing roles and using 'Repar Object Security' with the admin tool did not fix the issue. Is there another way that I can rebuild the security descriptors If anybody cna put me on the right track, I...

How do i change 15 months to read 1 year and 3 months?
Can some one please provide a formula on how to change months into years and months? Example, I have 15 months in a cell, but would like the next cell to be read 1.3. meaning 1 year and 3 months. Thanks Marty With that format, how are you going to differentiate between 1 year and 1 month and 1 year and 10 months? The numbers 1.1 and 1.10 are indentical to Excel, so you can't accomplish the visual difference (1.1 vs 1.10) with formatting. =DOLLARFR(A1/12,12) will give you 1.01 and 1.10 for the above examples. You need to format the cell with 2 decimal places so you see 1.10, not just ...

Problem upgrade from Mscrm 1.2 to 3.0
At the end of the upgrade i got this error message Failed to locate the Reporting Services Report Server configuration file. Verify the file specified in registry value HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\80\Reporting Services[RSConfigFilePath] Does anyone know what wrong? Thanks Mathias ...

CRM 1.2 Received today
Just been informed our copy of 1.2 has been received today lucky. so does it come later for customers? hurry and test it out! I heard it uninstalls 1.0 and then reinstalls with 1.2, hahaha..........keep us updated on your beta testing! -Gary "John O'Donnell" <csharpconsulting@nospam-hotmail.com-nospam> wrote in message news:exmsGGevDHA.3436@tk2msftngp13.phx.gbl... > Just been informed our copy of 1.2 has been received today > > ...

CRM 1.2: Custom Field with hyphen in name averts default value settings
unfortunately we have customized a lookup-field in crm whose name includes a hyphen months ago. Now this seems to be the reason why it is not possible to change the default value of this lookup. Any experiences with special characters in field names? What can I do now? Is there another reason for my problem with the default values? Any hotfixes? It's a pitty that CRM didn't prohibit the usage of the hyphen on creation of the field, but now months later this error is hard to correct! Had a similar problem with Crystal Reports as well, where a special character in the fieldname averts...

Check Box
I have a table linked from an SQL Server database that I would like to update through an Access form. I need to use a series of check boxes to adjust over 100 different fields, but I need the check boxes to update the fields in the linked table with 1 for yes instead of -1. The reason for this is that the other software that loads information from the database uses 1 as the value for yes and cannot be configured otherwise. I cannot use custom formatting on the table fields since they are linked. I am assuming that a VB script is the way to go, but am having trouble getting...

Controls Theme in MFC Dialogs (VC7.1 and 8.0)
Hi All, I make one application by VC wizard MDI and Dialog APP, both in VC7.1 and 8. The controls in thease apps don't have WINXP style. Why? How I enbed XP style in buttons, edits etc.. Thanks have a look at the link below..... http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnwxp/html/xptheming.asp Hi, Welcome to MSDN Newsgroup! Making controls XP style is even easier. You don't actually have to do anything because the OS is responsible for drawing the button and if you are running the program on XP then XP will draw it in the XP style. However, there is...

Convert a 2 page document to two 1 page documents
I have a Publisher 2003 (SP3) two sided brochure. I made it in to two documents, one for each side. When printing my Tektronix Phaser 850 still acts as if it is a two sided brochure and prints either page on the "back" of the single page. Anyone know how to fix this? John. Either combine the two files as one with two pages or when you print, select one file, File, Print, 1 of 1 and then select the second file, File, Print, 1 of 1 with the first pint job back in your printer, turned over. -- Don Vancouver, USA "JOHNFDS" <JOHNFDS@discussions.microsoft.com>...

Funnel all emails for a domain to 1 mailbox
I have an Exchange 5.5 server that currently receives email for 4 domains. I'd like to add a 5th domain and have all emails for the new domain funnel into 1 mailbox. So, emails sent to address1@newdomain.com, address2@newdomain.com, address3@newdomain.com and so on would all go to the same mailbox. I know I'd have to create the new domain and create new MX records for it, but I can't find anything that says "all emails for the new domain go to 1 mailbox." Is that even possible? Thanks, Mark. ...

Can print just 1 page from a 2 page of excel spreadsheet
Yes. File>Print>Print Range>Pages 1 to 1 Gord Dibben MS Excel MVP On Thu, 6 Aug 2009 02:44:01 -0700, Ellie Augur <EllieAugur@discussions.microsoft.com> wrote: ...

Macro command to turn on File Menu
Hi all, I have hidden the MENU BAR using a macro command on purpose. For the user to 'save/save as' his work, I plan to have a button on that worksheet. The user can open the FILE menu, using that button when pressed. Can someone suggest a macro command for that button to work the way I plan it to be if it is possible. Thank you so much. Zainuddin Z One way is to just show the File|Save as dialog. Option Explicit Sub testme() Application.Dialogs(xlDialogSaveAs).Show End Sub Zainuddin Zakaria wrote: > > Hi all, > > I have hidden the MENU BAR using a mac...

Availables : More that 3500 Solutions manuals and Test Banks (Part 1)
List of Solutions Manuals _________________________ contact me to : mattosbw1@gmail.com mattosbw1(at)gmail.com NOTE : "THIS SERVICE IS NOT AVAILABLE FOR : CHINA, INDIA, PAKISTAN, IRAQ, IRAN, PHILIPPINES, NORTH KOREA, NEPAL, BANGLADESH, SRI LANKA, MALDIVES & BHUTAN". If your wanted solutions manual is not in this list, also can ask me if is available (They are some only). Then if you need solutions manual only contact me by email. This same "list of titles" of texts with availables solutions manual is for download from : rapidshare.com/file...

How to get 1 ms delay ?
I need small delay in my application. But Sleep() is not a good one for getting small delay, becouse it is found that Sleep( 1) == Sleep( 2 ) == Sleep( 3 ) == Sleep( 4 ) ===== Sleep( 14 ) in my machine. ie, the the exact time taken by all calls are same. At last I got solution for that. int MySleep( int nSleepValue ) { HANDLE hTempEvent = CreateEvent( 0, true, FALSE, "TEMP_EVENT" ); timeSetEvent( nSleepValue, 1, (LPTIMECALLBACK)hTempEvent, 0, TIME_ONESHOT | TIME_CALLBACK_EVENT_SET ); WaitForSingleObject( hTempEvent, nSleepValue ); CloseHandle( hTempEvent ); r...

Print Command
When I edit a purchase order to cancel an un-received item; I get a "Purchase order edited status journal" which is the one that actually has the details of what I did. Then I get "Edit PO Status Distribution Detail" & "Edit PO Cost Variance Journal" reports, which contain no actual data. Is there a way to tell GP to not print if there is no change reported? I get frustrated by the amount of paper that prints that just basically have a heading & I trash. Yes if you go to Setup >> Posting >> and Posting again you can turn off rep...

New Question: Proper Way to use Budgeting?
As a stem from an earlier thread I posted: Is there a way to allocate income to a particular income fund, that when I look at my expense categories I'll be able to see how much money is available compared to how much I have budgeted for? Can I have identical Income and Expense accounts? And then link each expense category with a particular income category? Then, when I deposit money I can assign that deposit to numerous income accounts, and when I pay those expenses I can get a real-time listing of how much is "left" in the lincome versus the expense category? ie: Deposit c...

select only 1 record based on 1 criteria
My query results in a series of records. One of the fields contains a date. I'm only interested in 1 record, namely the one corresponding to the latest date. Can you help me? Try this --- SELECT YourTable.* FROM YourTable WHERE (((YourTable.YourDate)=(SELECT Max(YourDate) FROM YourTable as Temp ))); -- KARL DEWEY Build a little - Test a little "Gerry" wrote: > My query results in a series of records. One of the fields contains a > date. > I'm only interested in 1 record, namely the one corresponding to the > latest date. > Can you help me? > ...

automatic numbering 2.1, 2.2 etc
i have a policy that uses 1.1, 1.2 etc followed by 2.1, 2.2 etc followed by 3.1, 3.2 etc but i can't get the numbering to display automatically. I am using Office 2007 Word and would appreicate it someone could help. What kind of numbering? If you want to number your headings, the quickest solution is to pick the "1 Heading 1 1.1 Heading 2 1.1.1 Heading 3" format found at Home tab | Multilevel List. -- Stefan Blom Microsoft Word MVP "Jane" <Jane@discussions.microsoft.com> wrote in message news:98A8925D-E161-4C96-84B7-B5CAF7DF389A@micros...

Vlookup 1 column to the left
I would like to create a formula in cell c1 that will lookup C2:C100 and find the first occurance of "N" and use the number to the left (column B) in a further calculation. In this case 4 would be the result. I am adding data each day to the top of the spreadsheet so the rows will move down 1 cell that's why I need the formula Thanks Tom A B C 1 3 7 5 Y 8 6 Y 7 3 Y 9 8 Y 3 4 N 6 5 Y 7 9 N 3 4 N VLOOKUP only works to the right. Try this: =INDEX(B:B,MATCH("N",C:C,0)) -- Biff Microsoft Excel MVP "Tom" <tsande...