Format Column

I have a column with inmate id numbers in it.  I imported them from a
text based program.  I made a custom formatting for the column because
all the id numbers begin with zero.  IE 00112356 or 01555666 etc...
The format I used was 00000000 under custom.

The problem now is that I want to import them into access but access
doesn't see the zero at the beginning.  How can I make it show the
literal number including the zeros?  I tried adding the '01222555
before the number, but I would have to manually append 2000 records.  
Is there a faster way?

Morph.
0
morpheseus (17)
6/14/2004 3:46:00 AM
excel 39879 articles. 2 followers. Follow

2 Replies
747 Views

Similar Articles

[PageSpeed] 21

Hi
you could use a helper column with a formula like
=TEXT(A1,"00000000")

--
Regards
Frank Kabel
Frankfurt, Germany


Morpheseus wrote:
> I have a column with inmate id numbers in it.  I imported them from a
> text based program.  I made a custom formatting for the column
because
> all the id numbers begin with zero.  IE 00112356 or 01555666 etc...
> The format I used was 00000000 under custom.
>
> The problem now is that I want to import them into access but access
> doesn't see the zero at the beginning.  How can I make it show the
> literal number including the zeros?  I tried adding the '01222555
> before the number, but I would have to manually append 2000 records.
> Is there a faster way?
>
> Morph.

0
frank.kabel (11126)
6/14/2004 6:38:20 AM
Thanks Frank!  That worked perfectly.  I just imorted 1067 records
into my table by using this formula in a help column.  Then I just
deleted the original column ... leaving me only the correct formatting
in the final column.  

M.

On Mon, 14 Jun 2004 08:38:20 +0200, "Frank Kabel"
<frank.kabel@freenet.de> wrote:

>Hi
>you could use a helper column with a formula like
>=TEXT(A1,"00000000")

0
morpheseus (17)
6/16/2004 3:52:30 AM
Reply:

Similar Artilces:

Matching data in columns
I hope somebody can help me. I have a list in column C of 13000 names with a reference number against each of them in column D. The reference numbers are between 1 and 760 and each of the 13,000 names in column C has one of the 760 numbers against it in column D. In column F, I have the list of 760 numbers with text for each of these numbers in column G. I need to correctly insert the text for all these 760 names against my main list of 13,000 names. To try to explain it I want to: If number in any cell in column D equals the same number in any cell column F, then paste the text in...

How do add columns showing dob and years to work to give a retire.
I have a column showing date of birth and a column showing at what age they retire, 50, 55, 60. How do I add up the two to get a retirement date? Many thanks Claire Hi something like =DATE(YEAR(A1)+50,MONTH(A1),DAY(A1)) -- Regards Frank Kabel Frankfurt, Germany "Claire Le Monnier" <ClaireLeMonnier@discussions.microsoft.com> schrieb im Newsbeitrag news:8F892F61-F652-4177-A0C0-F25899C11CFA@microsoft.com... > I have a column showing date of birth and a column showing at what age they > retire, 50, 55, 60. How do I add up the two to get a retirement date? > Many than...

Locking and Hiding Columns
I have a spreadsheet that I need to share without the user being able to unhide certain columns yet still have the ability to enter data in the other fields. Any suggestions? Thanks -- JerryS Have you tried Locking all the the cells except those cells you want to allow user input, then hide the columns and protect the sheet with a password? Rob "JerryS" <JerryS@discussions.microsoft.com> wrote in message news:70FA2D38-3FBB-4B39-BF30-CC294BF05B29@microsoft.com... >I have a spreadsheet that I need to share without the user being able to > unhide certain columns yet ...

Condition Format #1
I have enclosed a chart with this explanation below. I am needing automate this chart according to the following rules. See chart also, if necessary. Thank you. First Whenever, a 3 digit numeric combination is entered into B, C and D cells and if each of the 3 digits are different (ie.3,7,2), then find the 3 cells on the same row between O and BG that have the corresponding boxed the 2 digit pairs. (O=01, P=02, Q=03, R=04, S=05, T=06, U=07, V=08, W=09, X=12, Y=13, Z=14, AA=15, AB=16, AC=17, AD=18, AE=19, AF=23, AG=24, AH=25, AI=26, AJ=27, AK=28, AL=29, AM=34, AN=35, AO=36, AP=37, AQ=38, AR=39...

auto-signature changes format on reply
What causes an autosignature to change format on replies? I have one user who is using Outlook 2000. When he replies to email, his auto-signature changes from single spacing to double spacing and loses some of its formatting. I tried creating a new auto-signature using word as his email editor, but the signature still changes on replies. Any suggestions or fixes? Thanks! Joann ...

Formatting lost when copied by formula!
Hi, I'm having a problem retaining format in a target cell where a formul "copies" the contents from another cell. Here's an example tha describes the problem in detail: Say I have the following text in cell A1: "Some text." Say, also, tha the word "text" is bolded. In cell B2 I have a formula, =A1, tha "copies" the content of cell A1. I can see the content of cell A1 i B2, but without the bold (or any other formatting, including backgroun color, etc.). My question, of course, is how do I retain the original formatting? Thank you -- Message ...

Auto-adjust column width
Hi all Is there a way to set a column width so that it auto-adjusts to the widest entry in the column? And what about the widest numerical entry, excluding word-wrapped cells containing text? I realise that I can double-click on the edge of the column header to achieve most of the desired result passively, but this does not work if the worksheet is protected. Thanks -- Return email address is not as DEEP as it appears Hi depending on your Excel version you can allow formating columns in the protection dialog. (I think starting with Excel 2002). In all other cases no chance but to remo...

Format in an unbound Text Box
Hi all, I have a form that I have put an unbound text box in, which displays the previous entries from my table. One of my fields is Current Time. Which on my form I set to Short Date (military time). How can I have my unbound text display Short time as well. here is my code (control source of my unbound text box). SELECT DISTINCTROW tblSITLog.[current time], tblSITLog.MONTH, tblSITLog.DAY, tblSITLog.SIT, tblSITLog.[NATURE OF iNCIDENT], tblSITLog.[Case Description], tblSITLog.Incident_Number FROM tblSITLog WHERE (((tblSITLog.[NATURE OF iNCIDENT])="CHRONO ENTRY")) ORDER BY...

GUID Columns in SQL 2005
Are there additional things you need to do when backing up and/or restoring databases which have tables containing guid/uniqueidentifier columns? Thanks in advance Please, ignore this question. "RG" wrote: > Are there additional things you need to do when backing up and/or restoring > databases which have tables containing guid/uniqueidentifier columns? > > Thanks in advance ...

Change Columns to Letters
I want to run a CountIf statement E.g =COUNTIF(A1:A100,"General queries") However, on the columns there are no letters, only numbers. So both the rows and the colums have letters. Does anyone know how i can change the columns from numbers to letters. Thanks for helping Dave -- David494 ------------------------------------------------------------------------ David494's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24482 View this thread: http://www.excelforum.com/showthread.php?threadid=391226 If anyone still knows how to do this then can you...

Outlook Burped and HTML Format Now Shows
Suddenly, HTML formatting is showing on my Outlook new mail messages composed with HTML. How can I delete this without having to resort to plain text emails? JTLaBelle wrote on Mon, 29 December 2008 10:56 > Suddenly, HTML formatting is showing on my Outlook new mail messages composed > with HTML. How can I delete this without having to resort to plain text > emails? Is it showing on messages you receive or messages you compose? If when you compose, does Alt+F9 hide it? If messages you receive, what version of outlook? version of windows? do you have all the latest updates i...

inserting more than one column
What would the VBA code be to insert 6 columns to the left of Col A? I recorded a macro but I know there has to be a quick line of code that will do the same things rather than 10 lines of code from the macro recorder. Thank you. Hi Columns("A:F").Insert Shift:=xlToRight -- Regards Roger Govier "SITCFanTN" <SITCFanTN@discussions.microsoft.com> wrote in message news:BD9FA6CB-C861-4BCB-93C9-E98232636594@microsoft.com... > What would the VBA code be to insert 6 columns to the left of Col A? I > recorded a macro but I know there has to be...

Using an equation to count non-duplicated items in column
I have a list of trailers in a sheet that are designated as T-####. Is there a way to count the number of non-duplicated entries in this column by using a formula? Thanks for any assistance, Please respond to hers2keep @ yahoo . com. Thanks, carla carla Try Chip Pearson's site........ http://www.cpearson.com/excel/duplicat.htm#CountingUnique BTW. The customary response is to the News Group, not email. That way we all learn. Gord Dibben Excel MVP XL2002 On 28 Aug 2003 14:30:58 -0700, cbr@saturnsea.com (carla) wrote: >I have a list of trailers in a sheet that are designated as...

Switching rows and columns
I am quite sure, in a long forgotten history, Lotus 1-2-3 had an option allowing one to switch column contents to rows and vice versa. I must be looking for the wrong keywords in help and google, can someone tell me how to swap rows and columns in excel? Alternatively, same question for OpenOffice Calc, which I also work with... THANKS! Sh. I don't use OO. But in Excel, you can select your range to transpose. then Edit|Copy Then select the top left cell of the new range edit|Paste special|check Transpose and then ok. You'll have to make sure that there is no overlap between the two...

Can the preset numbers in the column be changed?
I am trying to do a sign in sheet and would like horizontally at the top name, address etc, and name is in A1, address is in B1 etc. for example. Is there a way to make it so the numbering will reflect how many people sign in without adding a column with numbering? Thanks in advance. Teresa "=?Utf-8?B?c2hld2FoeWE=?=" <shewahya@discussions.microsoft.com> wrote in news:177A3969-6C4A-4633-BA49-32449EBBCABE@microsoft.com: > I am trying to do a sign in sheet and would like horizontally at the > top name, address etc, and name is in A1, address is in B1 etc. for >...

Total a column from sheet 2 based on value in sheet 1
Col B Col C Col F Mary Team 1 $331.00 George Team 1 $222.00 Sam Team 2 $186.00 Tom Team 2 $100.00 Above is an example of my data on Sheet 2. On Sheet 1, I want to total all the total funds raised per Team shown on Sheet 2. I am trying to create a summary of what each team raised. I tried using the formula: =SUM(('sheet 2'!F2:F482=Sheet1!B2)) and I get just a dash in my total col. Can anyone provide some help? Thanks Look in HELP for the SUMIF() function -- Kind regards, Niek Otten Microsoft MVP - Excel "Nee...

Formatting in 1000's
I use Excel 2000. Is there anyway to format numbers in 1000's? Right now I divide each cell by 1000 to get the results I want (=5,000,000.00/1000=5,000) and format to zero decimals. Thanks You could create a Custom Format : #,##0,_);(#,##0,) Format>Cells>Number tab Under Category, select Custom and enter: #,##0,_);(#,##0,) in the type box. NOTE: doing that won't change the actual value of the number, so 5,000,000 will still be 5 million, but it'll look like this: 5,000 Does that help? Regards, Ron "William Copeland" wrote: > I use Excel 2000. Is...

Reseting the last row/column
Hello all, I have a spreadsheet of data, lets say 35,000 rows long. So when I do a [Ctrl] + [End], it goes to the cell at row 35,000 (and the last column of the data). Now lets say that I run a macro that deletes 5,000 rows. So now the last row is 30,000. But if I do a [Ctrl] + [End] again, it still goes to row 35,000. Is there any way to reset the spread sheet so it knows that the last row/column of data has changed since row/column deletions? Thanks for any help anyone can provide, Conan Kelly Here is an article from Msft on the topic: http://support.microsoft.com/?kbid=2444...

PIVOT TABLE FORMATTING #2
If I have a pivot table with the following info: County, date, and lot #'s sold by county; how can i sort this horizontally so that the counties go across the top and under each county it sums up the number of lots? Hi, Put the Country field into the Column area. Do you really want to sum lot numbers or do you want to count lot number? To count or sum them they would be put in the Data area. Hope this helps because we don't have much to go on. If it does please click the Yes button. If not give us more details. What else is on your pivot table, are you counting, summing ...

Incoorrect date formats in Excel 2000
H I'm inporting a .txt file into excel. Some of the dates are coming across correctly i.e. DD/MM/YYYY however some come through in a different format (MM/DD/YYYY). I've checked the format of the cell and it's the same as the cells that are working correctly. I've even tried a paste special and just pasted the format but it doesn't seem to change anything. Any idea's would be appreciated!! If it's a .txt file, don't you get the import wizard to open up? And then you can specify the correct format for each field--including mdy or dmy. JimPNicholls wrote: > ...

Conditional Formatting Across Multiple Cells
I have data file that I want to conditional format Red/Green fill based on >= conditional on a single row of data. In Excel 2003 it was fairly simple to lock a row but not a column and by selecting all the cells to format it would change the column relative to the cell. Just having an issue transitioning to conditional formatting in 2007. Sample Data a b c d 1 goal 10 245 125 2 day1 8 200 76 3 day2 8 250 125 4 day3 15 250 130 5 day4 15 300 150 6 day5 0 100 0 7 Avg 9 220 96 Cells Rows 2 - 7 should be conditional on Row ...

format one data series, with error bars, and use it as default
In Excel can I format one data series with custom plus and minus standard deviation error bars and custom colors and then have the chart use that as the default? I couln't use a format painter to do. JBR, Yes, once you've created the chart with your custom error bars and colors, save it to the Custom Types Gallery: (1) Select (active) the chart. (2) Go to Chart -> Chart Type -> Custom Types Tab -> User Defined. (3) Click the Add button and you should see the Add Custom Chart Type Dialog box. (4) Enter a name and optional description for your chart. (5) Click OK and...

how do I chage mm/day format display to "workweek" display?
I'd like to change the default format in which the "weeks" are displayed, which is starting date of the week. Instead or in addition to that I'd like to display it as a work week. for e.g. the Week of july 3th would be 26ww while the week of july10th would be 27ww. ...

Hiding Columns In Excel
When I try to hide too many columns in Excell I get the following message and it won't let me hide the columns, "Cannot Shift Objects off Sheet.". Is there a workaround? What am I doing wrong? The following MSKB article may help: XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns http://support.microsoft.com/default.aspx?id=211769 Also, Dave Peterson recently posted this advice: '================ Someone with the same problem posted back that their comments were getting resized causing the trouble. Maybe enabling: Tools|Options|View|Comment and ...

conditional formatting graph
Is it possible to do conditional formatting on a bar graph? I need one bar to change to red if it is below the national average or stay green if it's above. I have found many postings on how to do something like this in Excel but nothing on how to do it in Access. Any help would be really appreciated. Thanks Becky ...