Number Format Problem

I am importing data from a program at work.  The data come in in th
format of ###@##(three numbers, a letter, and then 2 more numbers).
The problem is that when a code such as 123E04 is imported, exce
thinks it means 1.23E+04.  This is a problem becasue the data I need i
the E04 part.  (I use the RIGHT(cell,3) formula to get the portion 
want.)  Any ideas on how to get the number in the format I want?  
have tried changing the cell characteristics, and all kinds of stuff.

Thank

--
kola556
-----------------------------------------------------------------------
kola5567's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2461
View this thread: http://www.excelforum.com/showthread.php?threadid=38201

0
6/24/2005 4:54:26 PM
excel 39879 articles. 2 followers. Follow

1 Replies
432 Views

Similar Articles

[PageSpeed] 12

kola,

Rename the file with a .txt extension, then when you open it, Excel will
step you through an import wizard, during which you can tell Excel to treat
those values as strings.

HTH,
Bernie
MS Excel MVP


"kola5567" <kola5567.1r54ib_1119632706.4256@excelforum-nospam.com> wrote in
message news:kola5567.1r54ib_1119632706.4256@excelforum-nospam.com...
>
> I am importing data from a program at work.  The data come in in the
> format of ###@##(three numbers, a letter, and then 2 more numbers).
> The problem is that when a code such as 123E04 is imported, excel
> thinks it means 1.23E+04.  This is a problem becasue the data I need is
> the E04 part.  (I use the RIGHT(cell,3) formula to get the portion I
> want.)  Any ideas on how to get the number in the format I want?  I
> have tried changing the cell characteristics, and all kinds of stuff.
>
> Thanks
>
>
> -- 
> kola5567
> ------------------------------------------------------------------------
> kola5567's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=24614
> View this thread: http://www.excelforum.com/showthread.php?threadid=382015
>


0
Bernie
6/24/2005 6:46:36 PM
Reply:

Similar Artilces:

Activity report
I'm trying to write a report showing completed activities for users and another for accounts (clients). Boy, is it ugly! In version 1.2, with Crystal Reports, the HTML emails looked just fine -- it would format the HTML with no problem. How do I get the same result with SSRS? (version 2000) Thanks, Karla you mean you are trying to report on the contents of an email activity? If so this cannot be done without some custom solution, its also logged as a bug. ============================== John O'Donnell Microsoft CRM MVP Web - http://www.crowecrm.com Blog - http://www.crowe...

if isblank problem
I have written following formula on technical sheet of my workbook =if(isblank(q31:q36),0,sum(q31:q36)/4) which gives me correct answer true version ie 0 but when try to enter on summary sheet as =if(isblank(technical!q31:q36),0,sum(technical!q31:q36)/4) i get false version any ideas why? Thanks Tina Hi try =if(countblank(technical!q31:q36)=6,0,sum(technical!q31:q36)/4) -- Regards Frank Kabel Frankfurt, Germany tina wrote: > I have written following formula on technical sheet of my workbook > =if(isblank(q31:q36),0,sum(q31:q36)/4) > which gives me correct answer true version ...

Internet Mail Service Problem
This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C49F0B.EE172F50 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Team, For quite sometime my Internet Mail Service for Exchange 5.5 get stopped = of its own. My present environment setup is:-=20 1. I have 5 Exchange administrative console located at 3 three = different building.=20 2. My OS is Windows 2000 Server with SP4 3. Exchange 5.5 with SP4=20 4.Running NAV for Exchange built 2.18=20 After going through application logs, I found following E...

New Project, Different Problem: AutoFilter?
I'm creating a database of outside trainers. Each trainer can handle multiple categories of training. It is essential that the training manager be able to create lists for specific categories as the need arises, as well as being able to do more standard sorts such as Name. Rather than making multiple entries for each Trainer (one for each category, which may reach 10+), I'd like to allow multiple entries in the Category column utilizing a key for each category. It seems that AutoFilter would be the ideal method for creating this db. Well, actually Advanced AutoFilter, since...

Access ODBC problem: Excel cannot get float columns
I need some help I'm trying to get Access external data from an Excel workbook, using the MS Query feature. Everything is ok except when I try to fetch some tables that ODBC refuses to get data from. The error message tells that the MS Access ODBC driver doesn't allow some columns to be transmited because of its number of characters. The most strange thing is that I can see the data from MS Query correctly, but I caannot get it back from Excel. After some tries, I thing it occurs only with real typed columns. Can anybody help me? I use Windows XP Home + Office 2000 spanish versio...

Thread Problems
Found this from MSDN->codeguru, can't seem to figure out the problem.... class ThreadClass { protected: HANDLE m_hThread; //Thread handle bool m_bActive; //activity indicator DWORD m_lpId; //Thread ID public: ThreadClass(){} virtual ~ThreadClass(){Kill();} //Thread Management bool CreateNewThread() { m_hThread = CreateThread(NULL, 0, (LPTHREAD_START_ROUTINE)_ThreadFunc, (LPVOID) this, 0, (LPDWORD) &m_lpId); if (m_hThread == NULL) return false; m_bActive = true; return true; }; //Wait for thread to end bool Wait() {return (WAIT_OBJECT_0 == WaitForSingleObject(m_hT...

View Only Admin
I am having problems with the view only admin delegation. I put the appropriate group into the local admin on each exchange box but after a couple of days exchange removes this group. What is causing this and how do fix it so it leaves this security group in there. Thanks for your help -- Justin This can happen due to conflicting policies or rules. Try to enable Auditing for the security log and enable loging for Exchange in system manger and check results. "Justin" wrote: > I am having problems with the view only admin delegation. I put the > appropriate group ...

Internal Mail Problem
Hi all, I want to restrict some users to send & receive mail from internal user only, so I try to remove their smtp address and leave the x.400 address in the properties. This works well in Exchange 5.5 environment. But, how can I do this in Exchange 2003 environment, I cannot removed the smtp address in Exchange 2003, Please help, thanks. On Sun, 7 Aug 2005 00:46:12 +0800, <a> wrote: >Hi all, I want to restrict some users to send & receive mail from internal >user only, so I try to remove their smtp address and leave the x.400 address >in the properties. ...

Problem Printing w/Calendar Assistant
Every time I attempt to print a calendar using the Assistant it prints one page and then stops. I have tried to print multiple pages of a weekly calendar and each time it prints one week and then stops. I love the layout and colors of the calendar but I can not get more than one weekly calendar to print no matter how many weeks I select to be printed. Calendar Printing Assistant is known to be quirky. It works best with Outlook 2007. I cant say i know a fix for your issue. However, if you cant work around for this issue or you need a better printing solution, consider using...

problems with windows live mail
when windows live mail starts to download from my gmail pop account I always a box pop up saying "Internet Security Warning" dialog box "The server you are connected to is using a security certificate that could not be verified. "The certificate's CN name does not match the passed value." "Do you want to continue using this server?" I click yes and it works, connects and downloads messages. Until the email client is close and restarted that is. Then once again on the first access the security certificate message is displayed. How do I get this to w...

How can I add numbers but ignore the minus signs?
How can I add numbers but ignore the minus signs? In the following, the answer I'm after is 17 A1 -10 A2 5 A3 -2 =SUMPRODUCT(ABS(A1:A3)) Gord Dibben MS Excel MVP On Thu, 07 Jun 2007 04:19:52 GMT, Invalid <invalid@invalid.comINVALID> wrote: >How can I add numbers but ignore the minus signs? In the following, the >answer I'm after is 17 > >A1 -10 >A2 5 >A3 -2 > > Gord Dibben <gorddibbATshawDOTca> wrote in news:1o2f63pa84r8o6tdf65sa9ptpbv0nlt7u6@4ax.com: Thank you Gord. ----- > =SUMPRODUCT(ABS(A1:A3)) > > > Gord Dibben M...

check number > 10000
I have a checking account where my check numbers recently went over the 10000 mark and my credit union told me that the OFX format only goes up to 9999 so they start over at check number 1. I find it hard to believe that this could really be true. Anyone else run into this and have a better solution? Would another bank not have this issue? I use Money 2004 and it allows me to enter check numbers over 10000, but when I download my transactions from my Credit union it knows enough to match the check up correctly (IE: Credit union check number 10 match my entry of 10010) but when I accept ...

Outlook attachment problems
Dear Our oversea office sender using Outlook Sending message (HTML) insert a picture sending message to my office, when I have received the attachment auto duplicate one set in the message. I have asked the sender try remove the picture and FORWARD to my e-mail, but the attachment sill in the message and auto duplicate one set attachment. Out overseas office using MS SBS , user setup WG mode for outlook download message. I am not sure which part problems or virus effect. Please advice how to solve out the problems ASAP. JACKIE ...

one page in booklet format
How do I tell Publisher 2000 to print just page one and 4 and then pause while i reinsert the paper to print pages 2 and 3? duff wrote: > How do I tell Publisher 2000 to print just page one and 4 and then > pause while i reinsert the paper to print pages 2 and 3? File > Print > Pages 1 to 1 > OK > Print as separate booklet = No. Flip pages. File > Print > Pages 2 to 2 > OK > Print as separate booklet = No. -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Interesting problem in Money 2005 and MSN link
Money 2005 Premium. I swore I'd never link to MSN on the web but yesterday somehow got there by trying to set up an MBNA card. I finally figured it wasn't worth it so I removed everything - passport and all data. Here is the interesting thing. Previously, my mny file was 46MB after a shrink. After removing everything, it shrank to 41MB. I didn't really care since I had a backup but I decided to explore further. In putting everything through MSN and then disabling it all, it reversed the sign on 2 transactions (one in 1998, the other in 2003) but nothing else was touched. All 22,...

Very strange problems while running Great Plains on workstations
I notice that a few workstations in an office I support are having problems when they run Great Plains. Excel, Outlook, Word and Dynamics.exe are showing in the Application event log as being Hanging or Faulting. I also see Fault Bucket errors, but when I search online I cannot find any information online. Here is one of the Fault Bucket errors: 3:15:35 pm 28-Sep-06 Application Hang None 1001 N/A Fault bucket 296734104. Also, these workstations are experiencing problems printing PDF files. Has anyone out there seen this behavior and if so, how can these problems be fixed? Thank you, ...

Help With Problem: Nested IF Function, Office2K v Office2003
Hello Group, I'm looking for some help with a nested IF worksheet function. Shown below is a formula that I'm using in a model. =SUM(IF(worksheet!$A$3:$A$54=DATEVALUE("9/1/2005"),IF(worksheet!$B$3:$B$54="<90",worksheet!$C$3:$C$54,0),0)) If short, what I'm trying to do is scan the data in two columns and for any cells which meet the TRUE case in both columns, then sum the third numbers for all cases returning TRUE-TRUE. If either cell returns a FALSE, then enter 0. I created the original formulas and model in Office2000 and have used the model for more tha...

Problem with IE 8 03-09-10
my laptop just upgraded from vista to windows 7.using the IE 8 and decide that i would like to save some website links to fravorites.but i could not save any links.unknown reason.The previous saved links also were missing too.Tried using the key board short cuts but still unable to save any links. I would seriously like an assistance for this matter or a patch to solve the glitch.. THANKS !!!! [You may have upgraded your laptop to Win7 but it didn't do it on its own.] Unspecified error when adding a link to Favorites in Windows Vista and Windows 7 - The Winhelponline Bl...

problem sending mail to yahoo
Hi everyone, I posted a message regarding the same issue a few days ago. Running SBS2003. I can send mail to everyone apart from YAHOO. Gmail, Hotmail (any of the main freemail providers) with no problems. I've just disabled my SPAM filtering software to see if this helps. Has anyone else experienced similar problems??? HELP :-) Dave what specific error are you getting? "Dave Crozier" <mdcrozier@gmail.com> wrote in message news:ukNSYoveFHA.760@TK2MSFTNGP10.phx.gbl... > Hi everyone, > > I posted a message regarding the same issue a few days ago. > &...

find sum in list of of numbers
Hello, I have a list of numbers in a column and I need to find which numbers when summed together equal a figure. I have a list of invoice amounts that I need to match up with payments (the payments are always made for several invoices so I need to come up with sums of several invoices to get to this payment amount). An example would be I have this in the following section (A1:A10): $17,213.82 $4,563.02 $85,693.42 $1,166.01 $725.90 $580.09 $2,243.75 $240.16 $207.70 $725.90 I need to find which combination of these figures would sum $1,173.76. Thanks in Advance, Dza the troubled ...

References omit formatting and return cell address
In two cases of references between worksheets, the formatting from the original cell does not appear in the cell that it is referenced to. Case 1: Worksheet 1, A1 contains a currency formatted number - $2,000 Worksheet 2, A1 references the Workhseet 1, A1 cell using the = sign, yet it returns 2000 (unless I manually reformat the Workksheet 2 cell to Currency Case 2: Worksheet 3, A1 contains an apartment # - e.g. 4 Worksheet 4, A1 references this cell but returns the cell address - Worksheet2,!A1' - rather than the number 4. I tried different formats for the number 4,...

How to recall the Phone number ... what about other variables?
Hi just a simple question, I'm customizing my status.htm page. Without having any guide, I'm guessing almost every variable. One of the easy of them: Customer's PhoneNumber.... I tried with QSRules.Transaction.Customer.PhoneNumber but didn't bring my anything What is the right syntax? Does anybody have any list of variables you can call using QSRules ? Thanks Gustavo Gustavo, try this out: QSRules.Transaction.Customer.HomeAddress.PhoneNumber ...

Conditional Formatting based on deadlines #2
Excellent! Thanks! My brain gets confused between what you can use i formulas and what can be used in VBA. Alex Delamain Wrote: > Under conditional formatting set condition 1 > Formula is =+$E2<=NOW()+7 > > Then apply your forma -- madblok ----------------------------------------------------------------------- madbloke's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1442 View this thread: http://www.excelforum.com/showthread.php?threadid=27448 NOW() has a date and time component TODAY() has only a date component Ok, incorpo...

CRM 1-2 -> 3.0 Input string was not in a correct format. (upgrade
I've the same error during the upgrade from 1.2 to 3.0. I've tried without any opportunity to make sure not to have est. revenue data but the error is still there during upgrade. It happens during the ImportDefaultDataAction step near the end of the upgarde process. Otherwise 1.2 wasn't giving the error. Any idea? thanks in advance Refering to http://www.microsoft.com/Businesssolutions/Community/Newsgroups/dgbrowser/en-us/default.mspx?query=input+string&dg=microsoft.public.crm&cat=&lang=en&cr=US&pt=&catlist=8d3f188d-a0E9-40e4-86e2-46508a35c759&...

the cbSize problem
I've been talking for a long number of years about the scewup that named the first 32-bits of structures that contain a length. I just wrote a little script that did an analysis of the entire 1.4megalines of the Platform SDK (it fits nicely with my current task of revising my Systems Programming course for Vista). The script is rather simple, and analyzes the first line of all of the platform SDK structures, and if the first line corresponds to a set of rules it counts it. After analyzing 1.4 megalines of source code, I found 10,160 structures, of which there were 675 which met the rule...