Trim function #3

In cell A2, I use the trim function to trim cell A1 containing a date eg 
1202/09, the result in A2 is 39856. However, this 39856 is text and not date 
value. I therefore cannot convert it back to date after the trimming. Can 
someone please help? Many thanks...
-- 
exalan
0
exalan (10)
2/14/2009 3:17:01 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
583 Views

Similar Articles

[PageSpeed] 7

Just format the cell as date to display:
2/12/2009

-- 
Gary''s Student - gsnu200833
0
GarysStudent (1572)
2/14/2009 3:24:01 PM
On Feb 14, 7:17=A0am, exalan <exa...@discussions.microsoft.com> wrote:
> In cell A2, I use the trim function to trim cell
> A1 containing a date eg 1202/09, the result in A2
> is 39856. However, this 39856 is text and not date
> value. I therefore cannot convert it back to date
> after the trimming. Can someone please help?

=3Dvalue(trim(A1))

should do the trick.  You still might need to explicitly format the
cell as Date.  (But it worked for me without that when I did that in a
virgin cell.)
0
joeu2004 (766)
2/14/2009 3:50:33 PM
Try
=--TRIM(A1)


-- 
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=64437

0
2/14/2009 4:23:02 PM
Million thanks to Gary"s Student, joeu2004 and mdmackilop for their prompt 
response. I've got it!
-- 
exalan


"mdmackillop" wrote:

> 
> Try
> =--TRIM(A1)
> 
> 
> -- 
> mdmackillop
> ------------------------------------------------------------------------
> mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
> View this thread: http://www.thecodecage.com/forumz/showthread.php?t=64437
> 
> 
0
exalan (10)
2/14/2009 4:41:01 PM
Hi,

I know your problem has been solved, but I do have two questions -
1.  why are you entering dates 1202/09 (Excel will not treat these as dates) 
it should be 12/02/09
2.  what is the purpose of using TRIM?  It is designed to remove leading and 
trailing spaces plus any extra spaces in between words.  If you have a date 
in A1 then TRIM really does nothing.  
3.  If on the other hand 12/02/09 is text then rather than use =--TRIM(A1) 
just use =--A1  and format the cell accordingly.

-- 
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"exalan" wrote:

> In cell A2, I use the trim function to trim cell A1 containing a date eg 
> 1202/09, the result in A2 is 39856. However, this 39856 is text and not date 
> value. I therefore cannot convert it back to date after the trimming. Can 
> someone please help? Many thanks...
> -- 
> exalan
0
2/14/2009 7:59:01 PM
Hello Shana, responded yesterday but not sure why not yet posted, hence this 
repeated reply below:

Yes, I've simplified the question because I know the result is the same.

My real case is: I've donwloaded some data from "Pro Alpha" ESR system and 
done the "text to column" and "trim" routines. The date columns after "trim" 
all become text and I need to make use of the dates to continue my data 
analysis.

Anyway, your advice on no.3 is helpful to me. Thanks
-- 
exalan


"Shane Devenshire" wrote:

> Hi,
> 
> I know your problem has been solved, but I do have two questions -
> 1.  why are you entering dates 1202/09 (Excel will not treat these as dates) 
> it should be 12/02/09
> 2.  what is the purpose of using TRIM?  It is designed to remove leading and 
> trailing spaces plus any extra spaces in between words.  If you have a date 
> in A1 then TRIM really does nothing.  
> 3.  If on the other hand 12/02/09 is text then rather than use =--TRIM(A1) 
> just use =--A1  and format the cell accordingly.
> 
> -- 
> If this helps, please click the Yes button
> 
> Cheers,
> Shane Devenshire
> 
> 
> "exalan" wrote:
> 
> > In cell A2, I use the trim function to trim cell A1 containing a date eg 
> > 1202/09, the result in A2 is 39856. However, this 39856 is text and not date 
> > value. I therefore cannot convert it back to date after the trimming. Can 
> > someone please help? Many thanks...
> > -- 
> > exalan
0
exalan (10)
2/16/2009 4:21:01 AM
Reply:

Similar Artilces:

automatically add email address to contacts when replying mail in outlook 2003 #3
every time i send a reply or email to sometime how can i tweak outlook 2003 to save the email address to my contacts automatically?? - Thank you Mayur Mayur Patel <patelmb@vt.edu> wrote: > every time i send a reply or email to sometime how can i tweak > outlook 2003 to save the email address to my contacts automatically?? http://www.slipstick.com/contacts/addauto.htm -- Brian Tillman ...

number format #3
I want to change number format to indian number format form american number format for example: the american format is like 200,200,000.00 I want to change it as indian format 2,00,20,000.00 Adding to M ike's response, #","##","##","###.00;-#","##","##","###.00 to use the same format for negative numbers with a preceding minus sign -- HTH Bob Phillips "Mike" <mike22p@hotmail.com> wrote in message news:#gGdQskWDHA.652@TK2MSFTNGP10.phx.gbl... > #","##","##"...

Upgrading callouts and assemblies from 3.0 to 4.0
I have existing callouts and assemblies in 1.1 doct net versions. What would be the steps to upgrade these callouts and assemblies to 3.0? I have made calls to CRM and Metadata webservices from the assemblies. How much and what changes are required to these assemblies for making them useable in MS CRM 4.0? Please give me some ideas.. -- romeo!! For a complete conversion there are a few steps, depending on how you use the webservices this may take some work. 1. Create the new plug-in assembly classes: these classes must implement the IPlugin interface. In the Execute method you must ma...

smtp address #3
-- Geoff Kidd ...

crm 3.0
We put item serial numbers in the Invoice Products and print them on the Invoices. I would like to be able to view and search ALL Invoice Products to find them based on the Serial Numbers. Product Invoice [x] box is disabled in customize entity. How can I change this to enable so that the entities can easily be part of the Services menu? Our system is highly customized so I will accept any ideas or suggestions. ...

CRM 3.0 VPC May 2006 Extended Edition
hI am trying to load the VPC Image of CRM 3.0 May 2006 Extended Edition - I download the files - click on the zip file - shows crm.vhd - click on this and then winzip keeps asking for location of file 1 ----- what am I doing wrong or what am I missing? I select a folder and click ok but it keeps asking this -- Thanks in advance for any/all help. ...

Salesprocess not running after migration from CRM 3.0
Hi, We are trying to perform a test migration from 3.0 to 4.0. The migration goes OK, but after the migration all the sales process workflows have an error. The wfl's have the status "waiting", which is OK, because they are all waiting for an earlier created activity to reach the status "completed". Already found some info on an issue where workflow problems could occur when the website points to an IP adress in IIS, so changed that to "All unassigned". This didn't make any difference. After turning on tracing the tracefile for the AsyncService ther...

&[page]+2 not giving me 3....
Greetings I am using two versions of excel - one is excel 2000 where I us the &[page]+2 to give me page 3 and continue numbering the page bu when I send the document to a colleague using excel 2002 in win-x instead of printing page 3, 4 , 5 it prints page 12, 22, 32, 42, 52 an so on. Does ayone have a solution for this? Iva -- Message posted from http://www.ExcelForum.com In Excel 2002, there is an option on the File->Page Setup screen to start page numbering at 3 instead of Auto. If that option also exists in 2000, then you should be all set. Otherwise; I'm at an impass, t...

Sending problem #3
For the last few days, I'll be working online, press Send on an e-mail, and it just sits in the Outbox. Opening the e-mail from the Outbox and pressing Send yields the same result. Pressing Send and Receive on the toolbar has become the only way to send e-mail. I still have "Send immediately when connected" selected in my Options. I ran a virus scan with no results. These e-mails don't have large attachments or anything else unusual. Any ideas? Thanks, Ben ...

How to share Outlook 2003 with 3 computers (peer to peer)
How to I share my Outlook folders, calendar, contacts, tasks, notes? NO Exchange Server! ( I am peer to peer with 3 computers in my office) help! David Create a Personal Folders file on a drive/share that all 3 computer have access to. This PST file can be opened via the File > Open > Outlook data file on each machine. Now for the bad news, Outlook opens the file for exclusive access. Therefore when Machine A has Outlook running, Machines B/C cannot open/work with this shared personal folders file. By the way, there are 3rd party solutions that let multiple machines share t...

Send As permission #3
I am trying to grant a user Send As permission. I enable Advanced view, go to the Users object that I want to grant access to, go to the security tab, click advanced, and click Add, but I do not see Send As permission there. I can grant full mailbox access, but the grantee still shows "Sendt on Behalf Of" in the e-mail when they send for that user. What did I miss? Never mind....you have to do it on the domain controller, not from an exchange server.... "Tom Felts" <tfelts@mckennalong.com> wrote in message news:e3MFRAbUFHA.3312@TK2MSFTNGP09.phx.gbl.....

IIS/DNS Requirements for CRM 3.0 SBE?
I read my previous posting and throught rewording might make it easier to understand my question. The SBS 2003 server I am installing CRM onto has 3 IP addresses bound to it. 192.168.200.1-3 I am trying to install CRM using the .3 address and applying a specific host URL to it. For instance the .1 address is server.domain.local, the .2 address is app.domain.local, and I would like to use .3 as crm.domain.local. <A Records> for each host have been entered in DNS. The configuration wizard that appears after the initial install defaults to http://192.168.1.3:80 on the final screen ...

Method of calling a function where function name is stored in a variable or control
Is there a way to call a function (or sub for that matter) where the function/sub name is stored in a variable? For instance lets say I have a table that stores some function names. On a form I have a list box that displays the records in that table. For this example lets say they all accomplish similar things and all require the same numbef of arguments and none of them return a value. If Call were the right method to accomplis this it might look sometihng like this Dim lngArgument as Long lngArgument = 1234 Call Me.lstBox, lngArgument In this case Call would run the function...

Can't open attachments #3
On all the e-mails that I receive that show they have an attachment (the paperclip to the left of their addess), when I click on the paperclip, the attachments are not bold and when I attempt to open or save the attachment, nothing works. What do I need to do? Hi, I'm having the same problem. I'm using Outlook 2000, SP3 from MS Exhange 2000. Attachments are blocked. I know this is a Microsoft Security issue, however I beleive there is a registry fix for this. Any help would be much appreciated! Thanks, Marc >-----Original Message----- >Is this Outlook or Outlook Expr...

Overriding virtual function Equals affecting static Equals...
Please observe the code snippet: namespace OverrideEquals { class Human { public string name {get;set;} public override bool Equals(object obj) { Human h = obj as Human; if (h is Human) { if (this.name == h.name) return true; else return false; } else { Console.Write("Not a human:"); return false; } } public override int GetHa...

Attn: M02,3,4 users who use Passport/WinLiveID
You will get broken at the end of July, 2008. (That's about 45 days from now.) See http://support.microsoft.com/kb/894020. You should remove the Passport/WinLiveID from your file before that time. (File|Password Manager). There have been many indications this day would come. Now you all have a date certain. Does this also means backup files taken from M02,3,4 will be inaccessible, even if they are opened with a current version of Money? Although if you have to go back four, five or six years you probably have bigger issues. But I have at times opened older backup files, even five...

Blocked senders list #3
When I add senders to my blocked senders list, the next time I open Outlook, there is nothing in my blocked senders list. I have went into my registry under Current Users/Identities/Outlook Express/Rules and deleted the Rules folder with the understanding that it will be rebuilt. This however hasn't fixed the Blocked senders list. Is there something else I need to do? I'm a novice on my computer. Any help would be welcome! Thanks Kris ...

Junk E-mail Filter #3
Hi there, I am running Exchange 2003 SP2. When I use Outlook 2003, go to tools, then options, then Junk E-Mail, I get a message that says "The Junk E-mail Filter is not available for your Microsoft Exchange Server e-mail account because you are working online. To enable the junk email filter for this account , switch to cached exchange mode." But is this true? It seems not - because I am getting Junk email in my Junk E-Mail folder courtesy of IMF. So what is this message telling me? And therefore, how do I go about getting the white & black lists going for individuals if...

Deferred Revenue #3
We have a client that needs to enter GL Deferred transactions in different currencies. I know standard GP Deferral Module doesn't allow this. Does anyone know of any 3rd party apps that allows for this type of transaction? Ed ...

Copy and Paste #3
I would like to EXCHANGE the contents of one cell with another... e.g. cell A1 contains.... 25 cell A2 contains ... 65 Can I copy A1 into A2 using some form of paste special to EXCHANGE the 2 values? Thanks, Larry Hi Larry you would need a macro to achieve this change. There is no such 'paste special' option for this -- Regards Frank Kabel Frankfurt, Germany FLKulchar wrote: > I would like to EXCHANGE the contents of one cell with > another... > > e.g. cell A1 contains.... 25 > cell A2 contains ... 65 > > Can I copy A1 into A2 using some form of...

Prevent duplicate from 3 text
Dear All I have a form contais 3 text name, father_name,Sur_name how can i prevent the duplicate if a user entered John smith george and in another record he enterd the same name John smith george i want to display a msgbox to tell him that the name is duplicated best wishes -- Message posted via http://www.accessmonster.com On Thu, 08 Apr 2010 05:33:36 GMT, "ashraf_al_ani via AccessMonster.com" <u54845@uwe> wrote: >Dear All >I have a form contais 3 text >name, father_name,Sur_name > >how can i prevent the dup...

Exmerge #3
Where can I download the above for Exch 5.5? Hugh .. Try here .. http://www.mvps.org/exchange/Exmerge.htm -- David Kilpatrick "Hugh" <anonymous@discussions.microsoft.com> wrote in message news:e9c501c3f096$1c0d2510$a501280a@phx.gbl... > Where can I download the above for Exch 5.5? http://groups.google.com/groups?as_q=exmerge%20download&safe=images&ie=ISO-8859-1&as_drrb=q&as_qdr=m&lr=&hl=en Hugh wrote: > Where can I download the above for Exch 5.5? ftp://ftp.microsoft.com/PSS/Tools/Exchange%20Support%20Tools/Exmerge/Intel/ -- Hope that ...

problem with offline capabilities of Microsoft CRM 3.0
I have a problem with offline capabilities of Microsoft CRM 3.0. I defined 2 customized entities in the system, both connected to "Opportunity" entity in parental relationship. I defined in "Local Data" the same query for both entities but only one in downloaded while going offline. the query is "Parent Downloaded equals true " OR " Owner equals Current user" . There is no connection between the customized entities. What can be the reason for this situation? I'd check the definition of each entity; they may or may not have a parent (defi...

With Function
I hate to cut and paste anything into a module or procedures without understanding a little about how it works. I came across the following code that I could really use. It makes the label blink and using If/End If statements I could use it to flash warnings and 'expired' dates. With Label74 .ForeColor = (IIf(.ForeColor = 0, 255, 0)) End With But I cannot find any ref in any books and not even in the Access help pages for what is this WITH AND END WITH FUNCTIONS and what arguments go with it. Perhaps I don's know where to look for! ...

Paramters in MFC functions
Sorry for a basic question... In certain function I can use less parameters than indicated in the HELP and it works fine(for instance ChttpConnection::OpenRequest(...)). In the HELP it uses some 6 parameters but it works with two either. Can someone explain that please? Regards Rami "Rami" <rami@mandi.com> wrote in message news:%23N8znCIeIHA.4260@TK2MSFTNGP05.phx.gbl... > Sorry for a basic question... > In certain function I can use less parameters than indicated in the HELP > and > it works fine(for instance ChttpConnection::OpenRequest(...)). In the HELP &...