Using formatted cells in formulas

I have been provided a worksheet with a list of 5-digit part numbers that
the creator of the worksheet formatted as zip code to preserve leading
zeros. I need to use these cells in a formula which uses the "&" operator to
tack on a suffix. For example:

Original part number cell A1 = 2345 (with zip code formatting displays as
02345)

I need cell A2 to be 02345XX, so I'm using the following formula: A1&"XX".

Problem is, when I do this, Excel drops the leading zero and displays
2345XX. Is there a way to have Excel use the formatted display string for
cell A1 instead of the underlying numeric value?


0
frank4729 (20)
6/7/2005 3:06:25 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
280 Views

Similar Articles

[PageSpeed] 24

Frank,

Use something like

=TEXT(A1, "00000") & "XX"

-- 
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Frank Marousek" <frank@n-c-systems.com> wrote in message 
news:OsU9jH3aFHA.2288@TK2MSFTNGP14.phx.gbl...
>I have been provided a worksheet with a list of 5-digit part numbers that
> the creator of the worksheet formatted as zip code to preserve leading
> zeros. I need to use these cells in a formula which uses the "&" operator 
> to
> tack on a suffix. For example:
>
> Original part number cell A1 = 2345 (with zip code formatting displays as
> 02345)
>
> I need cell A2 to be 02345XX, so I'm using the following formula: A1&"XX".
>
> Problem is, when I do this, Excel drops the leading zero and displays
> 2345XX. Is there a way to have Excel use the formatted display string for
> cell A1 instead of the underlying numeric value?
>
> 


0
nothanks4548 (968)
6/7/2005 4:23:26 PM
Excel is simply displaying the zeros, which are not really there (tr
=Len(A1) to see).  Using that principle, we can test the length of A
and add zeros as needed:

in cell A2:
=IF(LEN(A1)=4,"0"&A1&"XX",IF(LEN(A1)=3,"00"&A1&"XX",A1&"XX"))

I am assuming no part code number is fewer than three 'true' digit
(00123)

HT

--
swatsp0

-----------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1510
View this thread: http://www.excelforum.com/showthread.php?threadid=37708

0
6/7/2005 4:54:45 PM
Two good suggestions... thanks!


-- 

"swatsp0p" <swatsp0p.1q9n6e_1118163906.3036@excelforum-nospam.com> wrote in
message news:swatsp0p.1q9n6e_1118163906.3036@excelforum-nospam.com...
>
> Excel is simply displaying the zeros, which are not really there (try
> =Len(A1) to see).  Using that principle, we can test the length of A1
> and add zeros as needed:
>
> in cell A2:
> =IF(LEN(A1)=4,"0"&A1&"XX",IF(LEN(A1)=3,"00"&A1&"XX",A1&"XX"))
>
> I am assuming no part code number is fewer than three 'true' digits
> (00123)


0
frank4729 (20)
6/7/2005 7:09:35 PM
Reply:

Similar Artilces:

Originating Document name blank when using cashbook
Once a transaction is processed via casbook the payee/beneficiery is blank in the financial/trial balance detail under the Originating document name. The only way around it is via Smartlist including columns description and reference. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in th...

Force hyperlink to use specific browser?
I have Firefox set up as my default browser, which is the way I want it. Unfortunately, some Microsoft-subservient outfits design their websites so that they only work with IExplorer. Is there a way to force a hyperlink to use a specific browser? Hi Enigman, This will open a link in IE no matter what your default browser is: Public Sub gLaunchInIE(rsURL As String) Dim ie As Object Set ie = CreateObject("InternetExplorer.Application") ie.navigate rsURL ie.Visible = True Set ie = Nothing End Sub -- Regards, Jake Marx MS MVP - Excel www.longhead.com...

Saving a custom format?
Greetings, I created a custom number format for use on a particular workbook. Now that custom format is available for use anywhere on that workbook. However, the custom format is not available in a new workbook or any other existing workbook. Is there a way to save the custom format so that it is available in any workbook? Thanks! Jim Orson... Hi Try putting the format in a blank workbook called Book1 and save this in your XLSTART folder -- Regards Frank Kabel Frankfurt, Germany Jim Orson wrote: > Greetings, > > I created a custom number format for use on a particular work...

Conditional formatting
Hi, i need a way to use conditional formatting or another way to highlight cells based on duplicate names group. Ex: 1 john 2 john 3 john 4 mary 5 mary 6 jim 7 cris 8 cris 9 cris I need all cells with john to be highlighted with one colour, the next group (mary) with another color, the next group (jim) with another color....and so on. All names will be ascending. Can this be done? Thanks! Try the below macro with data in ColA Sub Macro1() Dim lngRow As Long, blnChange As Boolean For lngRow = 2 To Cells(Rows.Count, "A").End(xlUp).Row I...

How to make excel understand to copy a Range of cells which has data in it
I have 2 excel files regarding daily values of two equities that I download from the internet on a regular basis. Each xls file has the following columns and rows for that particular equity. For eg. EquityA.xls will have : Symbol Date Open High Close Turnover A 01-jan-09 43.5 52 46 1496.53 A 02-jan-09 47 49 42 1322.44 A 03-j...

Date woes using VBA
I have a table with a field for dates, which contains four rows. I am attempting to use the first date to populate a field in a different table, using an update query written in SQL. The syntax is: rs.ActiveConnection = DR rs.Open "AttDatesT" With rs rs.MoveFirst datDate = .Fields("DateId") MsgBox strDate strSQL1 = "UPDATE [Students-T] SET [Students-T].Date1 = " DoCmd.RunSQL strSQL1 & datDate End With rs.Close The message box displays the date correctly, eg 10/02...

using publisher to print business cards
Hello, I am using publisher 2003 to print my business cards on a HP business inkjet printer., I have printed other cards w. no problem, but I have a new logo that I am using and it will not print my logo just the name addresss etc info. I have done a spysweeper, I have done disk cleanup, defrag, turned the computer off and on several time. ran anti virus.. I dont know what to do next. someone else has printed the business card from their computer and says it works just fine. I can print the logo by itself and it is beautiful but when I add it to the cards it doesnt print the logo. ...

Cells are too small!
When I activate Excel 97, the cells of Book1 are too small to such an extent that I can't view columns & rows names!). How do I do now? TIA. Tytoque. is the zoom set to higher than 100% view>zoom >-----Original Message----- >When I activate Excel 97, the cells of Book1 are too small to such an extent >that I can't view columns & rows names!). >How do I do now? > >TIA. > >Tytoque. > > > > >. > Tytoque Are you in "Page break" view? View>Normal Perhaps the Zoom is set to a number far less than 100%? Gord Dibb...

how to send layout (color, bold ..) to a excel cell using objStreamWriter?
Hi, i use objStreamWriter for writing data to an excel sheet. objStreamWriter = New StreamWriter(objFileStream) objStreamWriter.WriteLine("title" & chr(9) & "name") etc ... This works perfect. But is it also possible to give a cell a color, or to make the text sent to a cell bold? Thanks Luc Hi, Which format do you use ? For now it seems you are using CSV which allows only to import data. If you want to use formatting you could use HTML (Excel is able to import HTML tables), XML (either the old xml format or what is now the native for...

update form on change of field used in expression
I have one form based on a table, which calculates the unit costs based on the total price and number of units received. I created this form some time ago, and it works perfectly. The moment I change the total cost or units, it updates the unit price field accordingly. I created a similar form, also using an expression that determines the profit if sold at a price (the 'sold at' field). However this form does not change the calculated field until I F5 or refresh. I cannot see what I did differently in the original form, however would like this new form to update im...

Blank Cells vs. Zero Values
Column AZ contains cells that are either zero or are blank (meaning no value has been entered in the cell). The formula below is referencing the blank cells as zeroes. But I only want it to reference the cells that have zero in them. How do I modify the formula so it ignores the blank cells? {SUM(IF(Chart1!$AZ$2:$AZ$10000=0,Chart1!$F$2:$F$10000),)} Thanks, Bob Array entered =SUM(IF(Chart1!$AZ$2:$AZ$10000<>"",IF(Chart1!$AZ$2:$AZ$10000=0, Chart1!$F$2:$F$10000))) If this post helps click Yes --------------- Jacob Skaria "bob" wrote: > C...

How to ignore hidden columns in an "Average" formula?
If an "average" formula includes multiple columns, and those columns change each month by hiding old months (at end) and adding new months (at beginning) what can be done, by macro or in formula) to ignore the hidden columns? Try this if you can use 101 in your Excel version =SUBTOTAL(101,A1:C1) -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "DotK" <DotK@discussions.microsoft.com> wrote in message news:D6F36A22-BDE4-4836-98AE-028A616A9300@microsoft.com... > If an "average" formula includes multiple columns, and thos...

Conditional Formatting II
Hello all. I received help from Frank yesterday regarding the above topic, colorizing cells based on different values. Where I am now running into a problem is transferring that same data into another sheet using a function to retrieve the data. More specifically, I have 12 sheets with data, one for each month. The 13th sheet compiles all the monthly data into a yearly calendar format extrapolating the data from the 12 monthly sheets. The problem is that the text (or values) copy over, but the bg color doesn't. Any advice Thanks, Chris Hi Chris you're probably out of luck. Formulas ca...

PiVot Table Formatting
I have a PiVot Table like this: Product ID Factory Sales 100 A 500 B 500 S 200 100 Total 1200 101 A 500 B 500 S 200 101 Total 1200 I want the the raws with total are in bold font. How can I do it? Thanks in advance. To enable selection -- On the Pivot toolbar, choose PivotTable>Select Click on Enable Selection To format the subtotals -- Move the pointer to the left edge of a subtotal heading in the pivot table. When the black arrow appears (like the one that appears when the pointer is over a row button), click to select all the subtotal rows for that field in the pivot table...

Use of ADMIN as user/account name
We have been using an email address with "admin" as the user/account name for customers to send general documents to. (in other words the full email address looked something lke this "admin@xyz.com") We will be moving to an MS Exchange email system and were told that you cannot use "admin" as a user/account name on an MS Exchange system because it will cause a variety of problems. Is this true? If so, what problems might occur? On 20 Mar 2007 08:09:17 -0700, "Lanranger" <mike.smith@accesseforms.com> wrote: >We have been using an email addr...

8000 Watt Generator - NEW
Still in box. Bought from Home Depot last year and never got around to hooking it up. Paid $1,300 for it. Will sell for $1,000 negotiable. Made by Devilbiss - 14 HP Briggs & Straton engine. Never even taken out of the box!!! Email pmassey at nc dot rr dot com for more details. ...

Does anyone reccommend CRM for Real Estate offices using Exchange.
I'm looking for advice on wether or not this will work with Exchange Server. I have Business Contact Manager but it does not. CRM does work with Exchange. in fact, Exchange is the only way to send/receive email with the rpoduct. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Wed, 1 Dec 2004 11:19:03 -0800, "Art Luciano" <ArtLuciano@discussions.microsoft.com> wrote: I'm looking for advice on wether or not this will work with Exchange Server. I have Business Contact Manager but it does not. Ar...

Updating PST file to 2007 Format
Hi all, I'm running Outlook 2007 and looking at my PST file that's been coming forward since Office 2000 it shows that the PST file is a 97-2002 mode file. Is there any way to upgrade the PST file to the latest format? And maybe more to the point is there any advantage to doing so? Everything seems to work fine, other than the slowness of the file (it's about 130megs) and I'm hoping moving up to the latest format might help performance? +++ Rick --- File->New->Outlook Data File->Personal folders file. This will be in = the new unicode format that allows st...

Outlook 2007 Ignoring Mail Format Configured for Contact
I need to send emails to a particular internet recipient (i.e., not in exchange) in Plain Text. I attempted to configure this in the following ways: Attempt 1: 1) Open Contact window 2) Double click Email... field under Internet section 3) Set Internet format to "Send Plain Text only" Attempt 2: 1) Open Contact window 2) Right click Email... field under Internet section 3) Select Send Option... 4) Check "I want to specify the format..." 5) Select "MIME" -> "Plain Text" Attempt 3: 1) Same as steps 1-4 in Attempt 2 2) Select "P...

Formatting
I am working in Microcsoft Publisher.I have typed in text and then inserted a picture. How do I get the text to wrap around the picture. When working in Word I could just insert a picture and the text could be wrapped around it. In publisher the text is confined to a text box. Whatever I seem to do the picture remains the same only the size of the text box alters and text will not wrap aound the picture.I have tried using edit points but have got in a real mudle I am desperate now. Any help please? What version Publisher? Are you sending the image to the front? Click the dog icon, (picture t...

Use of Previous Version Access Databases in Access 2003
I read the following in an MS support page: "If you want to save the Access file as an MDE (MDE file: Microsoft Access database (.mdb) file with all modules compiled and all editable source code removed.) or ADE (ADE file: A Microsoft Access project (.adp) file with all modules compiled and all editable source code removed.) in Access 2002 or later, you must convert the file to Access 2002 - 2003 file format. Otherwise, you don't necessarily need to convert a Microsoft Access 2000 file to the Access 2002 - 2003 file format." I need to support a few databases (almost always in A...

Help using a formula when pointing to a cell #2
NOTE: To clarify what I have described below I have attached screenshot. I am trying to fill a table (18 rows by 50 columns) on a seperate shee (called 'Data') from another sheet (called 'Sheet1') containing column of 900 cells of data. The thing is each of the 900 cells is actually 4 merged rows (so ther are actually 3600 rows). So the cells containing the data are labele as $J7, $J11, $J15, $J19...... etc. The table must be filled sequentially (i.e. from left to right the onto the next line) from the column of 900 cells containing the data. How can I do this ? Is i...

Filter colored cells
Hi Friends I compared 2 columns ( A & D) its number, it is not equal i filled RED color appropriate cell. My quest is how to filter RED color cells? Plz help, Thanks in advance Regards Venky See http://www.rondebruin.nl/easyfilter.htm, it has a colour filter facility. 2007 has it built in. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <mvenkatesan45@gmail.com> wrote in message news:1185515219.702352.284050@e16g2000pri.googlegroups.com... > Hi Friends > I compared 2 columns ( A & D) its number, it is not equal i filled R...

Fit large amount of text in 1 cell
I am creating a table where several of the cells contain 200 or more words each. When I adjust the row height to accommodate the amount of text, I am unable to see all of the text. I have to adjust the column width and make it fairly large to view all of the text. Is there some setting that I am missing? Or some character row limit per cell? Thanks for any help you can provide. Select your cells/rows of choice, then hit Format, Cells, and the Alignment tab. There, you should have the Wrap Text box checked. Close out of that, then go back and with your selection still highlighted, ...

Keep format of an excel pivotdiagram
Dear newsgroup. I usually spend quite some time to format excel-diagrams (based on pivottables) to my black-and-white printer. And every time I press "Update" or change data the standardformat comes back (coloured lines and markers). 1) Is it possible to stop this? 2) Is it possible to make b/w-formatting a standard? Regards Anders Juul Anders Hi, I am having a similar problem. I have a pivot chart with a page field whereby I can choose a number of datasets to present. I set the format to the way I want - lines on 2 axis - but when I change the field setting it returns to t...