Need Help Formatting a 6-digit number

My excel worksheet prints a barcode sheet from data that is pulled from
a database.   The format for my "Client ID" number must always be 6
digits, but the format pulled from the database is sometimes
inconsistent, and I need to find a way to correct this in my Excel
worksheet.

If the number pulled from the database is only 5 digits, I need for
Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
Excel to drop the first digit and retain only the right-most 6 digits.
 Like so:

Database         Desired Excel Format:
55555     ->     055555
0555555   ->     555555

The formula in the "Client ID" cell is as follows.  (It includes a a
leading "*" before and after the number because I am printing barcode
sheets.)   I thought the "000000" would format the number to 6 digits,
but it isn't working.

="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"

Does anyone know how I can restrict my Client ID to 6 digits... either
by dropping (or adding) the leading digit pulled from the database?

Thanks,
Jessi

0
11/5/2005 11:14:41 PM
excel 39879 articles. 2 followers. Follow

9 Replies
841 Views

Similar Articles

[PageSpeed] 43

How about

=RIGHT(REPT("0",6-MIN(6,LEN(A1)))&A1,6)

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


<jessiright77@gmail.com> wrote in message
news:1131232481.273463.227030@g47g2000cwa.googlegroups.com...
> My excel worksheet prints a barcode sheet from data that is pulled from
> a database.   The format for my "Client ID" number must always be 6
> digits, but the format pulled from the database is sometimes
> inconsistent, and I need to find a way to correct this in my Excel
> worksheet.
>
> If the number pulled from the database is only 5 digits, I need for
> Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
> Excel to drop the first digit and retain only the right-most 6 digits.
>  Like so:
>
> Database         Desired Excel Format:
> 55555     ->     055555
> 0555555   ->     555555
>
> The formula in the "Client ID" cell is as follows.  (It includes a a
> leading "*" before and after the number because I am printing barcode
> sheets.)   I thought the "000000" would format the number to 6 digits,
> but it isn't working.
>
> ="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
> "000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
>
> Does anyone know how I can restrict my Client ID to 6 digits... either
> by dropping (or adding) the leading digit pulled from the database?
>
> Thanks,
> Jessi
>


0
bob.phillips1 (6510)
11/5/2005 11:19:35 PM
=IF(LEN(A1)=5,0&A1,RIGHT(A1,6))

Vaya con Dios,
Chuck, CABGx3



<jessiright77@gmail.com> wrote in message
news:1131232481.273463.227030@g47g2000cwa.googlegroups.com...
> My excel worksheet prints a barcode sheet from data that is pulled from
> a database.   The format for my "Client ID" number must always be 6
> digits, but the format pulled from the database is sometimes
> inconsistent, and I need to find a way to correct this in my Excel
> worksheet.
>
> If the number pulled from the database is only 5 digits, I need for
> Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
> Excel to drop the first digit and retain only the right-most 6 digits.
>  Like so:
>
> Database         Desired Excel Format:
> 55555     ->     055555
> 0555555   ->     555555
>
> The formula in the "Client ID" cell is as follows.  (It includes a a
> leading "*" before and after the number because I am printing barcode
> sheets.)   I thought the "000000" would format the number to 6 digits,
> but it isn't working.
>
> ="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
> "000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
>
> Does anyone know how I can restrict my Client ID to 6 digits... either
> by dropping (or adding) the leading digit pulled from the database?
>
> Thanks,
> Jessi
>


0
croberts (1377)
11/6/2005 1:57:53 AM
And one or two more...

=RIGHT(REPT("0",6)&A1,6)
or
=RIGHT("000000"&A1,6)



jessiright77@gmail.com wrote:
> 
> My excel worksheet prints a barcode sheet from data that is pulled from
> a database.   The format for my "Client ID" number must always be 6
> digits, but the format pulled from the database is sometimes
> inconsistent, and I need to find a way to correct this in my Excel
> worksheet.
> 
> If the number pulled from the database is only 5 digits, I need for
> Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
> Excel to drop the first digit and retain only the right-most 6 digits.
>  Like so:
> 
> Database         Desired Excel Format:
> 55555     ->     055555
> 0555555   ->     555555
> 
> The formula in the "Client ID" cell is as follows.  (It includes a a
> leading "*" before and after the number because I am printing barcode
> sheets.)   I thought the "000000" would format the number to 6 digits,
> but it isn't working.
> 
> ="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
> "000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
> 
> Does anyone know how I can restrict my Client ID to 6 digits... either
> by dropping (or adding) the leading digit pulled from the database?
> 
> Thanks,
> Jessi

-- 

Dave Peterson
0
petersod (12005)
11/6/2005 2:31:51 AM
On 5 Nov 2005 15:14:41 -0800, jessiright77@gmail.com wrote:

>My excel worksheet prints a barcode sheet from data that is pulled from
>a database.   The format for my "Client ID" number must always be 6
>digits, but the format pulled from the database is sometimes
>inconsistent, and I need to find a way to correct this in my Excel
>worksheet.
>
>If the number pulled from the database is only 5 digits, I need for
>Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
>Excel to drop the first digit and retain only the right-most 6 digits.
> Like so:
>
>Database         Desired Excel Format:
>55555     ->     055555
>0555555   ->     555555
>
>The formula in the "Client ID" cell is as follows.  (It includes a a
>leading "*" before and after the number because I am printing barcode
>sheets.)   I thought the "000000" would format the number to 6 digits,
>but it isn't working.
>
>="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
>"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
>
>Does anyone know how I can restrict my Client ID to 6 digits... either
>by dropping (or adding) the leading digit pulled from the database?
>
>Thanks,
>Jessi


=TEXT(RIGHT(A1,6),"000000")


--ron
0
ronrosenfeld (3122)
11/6/2005 2:39:11 AM
On 5 Nov 2005 15:14:41 -0800, jessiright77@gmail.com wrote:

>My excel worksheet prints a barcode sheet from data that is pulled from
>a database.   The format for my "Client ID" number must always be 6
>digits, but the format pulled from the database is sometimes
>inconsistent, and I need to find a way to correct this in my Excel
>worksheet.
>
>If the number pulled from the database is only 5 digits, I need for
>Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
>Excel to drop the first digit and retain only the right-most 6 digits.
> Like so:
>
>Database         Desired Excel Format:
>55555     ->     055555
>0555555   ->     555555
>
>The formula in the "Client ID" cell is as follows.  (It includes a a
>leading "*" before and after the number because I am printing barcode
>sheets.)   I thought the "000000" would format the number to 6 digits,
>but it isn't working.
>
>="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
>"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
>
>Does anyone know how I can restrict my Client ID to 6 digits... either
>by dropping (or adding) the leading digit pulled from the database?
>
>Thanks,
>Jessi

Can you format the cell (use the special) field and use "000000" as
the format? That should pad the leading open positions with a zero and
trim any extra (longer than 6 digits) number to 6 digits.

Norman
0
n.heyen (8)
11/6/2005 4:13:30 AM
On Sun, 06 Nov 2005 04:13:30 GMT, NormanHeyen <n.heyen@insightbb.com>
wrote:

>On 5 Nov 2005 15:14:41 -0800, jessiright77@gmail.com wrote:
>
>>My excel worksheet prints a barcode sheet from data that is pulled from
>>a database.   The format for my "Client ID" number must always be 6
>>digits, but the format pulled from the database is sometimes
>>inconsistent, and I need to find a way to correct this in my Excel
>>worksheet.
>>
>>If the number pulled from the database is only 5 digits, I need for
>>Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
>>Excel to drop the first digit and retain only the right-most 6 digits.
>> Like so:
>>
>>Database         Desired Excel Format:
>>55555     ->     055555
>>0555555   ->     555555
>>
>>The formula in the "Client ID" cell is as follows.  (It includes a a
>>leading "*" before and after the number because I am printing barcode
>>sheets.)   I thought the "000000" would format the number to 6 digits,
>>but it isn't working.
>>
>>="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
>>"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
>>
>>Does anyone know how I can restrict my Client ID to 6 digits... either
>>by dropping (or adding) the leading digit pulled from the database?
>>
>>Thanks,
>>Jessi
>
>Can you format the cell (use the special) field and use "000000" as
>the format? That should pad the leading open positions with a zero and
>trim any extra (longer than 6 digits) number to 6 digits.
>
>Norman

Sorry, make that Format _> Cells _> Custom.
0
n.heyen (8)
11/6/2005 4:17:18 AM
I think you'll find that a custom format of 000000 won't drop any digits if the
value is has more than 6 digits.



NormanHeyen wrote:
> 
> On 5 Nov 2005 15:14:41 -0800, jessiright77@gmail.com wrote:
> 
> >My excel worksheet prints a barcode sheet from data that is pulled from
> >a database.   The format for my "Client ID" number must always be 6
> >digits, but the format pulled from the database is sometimes
> >inconsistent, and I need to find a way to correct this in my Excel
> >worksheet.
> >
> >If the number pulled from the database is only 5 digits, I need for
> >Excel to add a leading 0.   Likewise, if it is 7 digits, I need for
> >Excel to drop the first digit and retain only the right-most 6 digits.
> > Like so:
> >
> >Database         Desired Excel Format:
> >55555     ->     055555
> >0555555   ->     555555
> >
> >The formula in the "Client ID" cell is as follows.  (It includes a a
> >leading "*" before and after the number because I am printing barcode
> >sheets.)   I thought the "000000" would format the number to 6 digits,
> >but it isn't working.
> >
> >="*" & IF(ISNUMBER($C$5)=TRUE, TEXT(UPPER(SUBSTITUTE($C$5, " ", "!")),
> >"000000"), UPPER(SUBSTITUTE($C$5, " ", "!"))) & "*"
> >
> >Does anyone know how I can restrict my Client ID to 6 digits... either
> >by dropping (or adding) the leading digit pulled from the database?
> >
> >Thanks,
> >Jessi
> 
> Can you format the cell (use the special) field and use "000000" as
> the format? That should pad the leading open positions with a zero and
> trim any extra (longer than 6 digits) number to 6 digits.
> 
> Norman

-- 

Dave Peterson
0
petersod (12005)
11/6/2005 1:44:54 PM
Thanks for all the suggestions!  They were very, very helpful.

The formula that worked for my particular situation was:
="*" & RIGHT(REPT("0",6-MIN(6,LEN($C$5))) & $C$5,6) & "*"

Much obliged!

Jessi

0
11/6/2005 10:55:53 PM
Well, you certainly had enough choices <vbg>

Bob


<JessiRight77@yahoo.com> wrote in message
news:1131317753.573104.272280@z14g2000cwz.googlegroups.com...
> Thanks for all the suggestions!  They were very, very helpful.
>
> The formula that worked for my particular situation was:
> ="*" & RIGHT(REPT("0",6-MIN(6,LEN($C$5))) & $C$5,6) & "*"
>
> Much obliged!
>
> Jessi
>


0
bob.phillips1 (6510)
11/6/2005 11:37:16 PM
Reply:

Similar Artilces:

Multiple Condition Formatting
Hi, Can anyone help me on this: =IF(AND(F65<>0,G59="SELECT CUSTOMER"),APPLY FORMATTING, DON'T APPLY FORMATTING) I have been exploring the Conditional Formatting in 2007 and I may be wrong but I don't see a way to set a condition whereby two(2) logical conditions must be met for it to apply the formatting. Any ideas?? http://excel.tips.net/Pages/T002980_Conditional_Formats_that_Distinguish_Blanks_and_Zeroes.html This could easily be applied to other conditions. Mike "Gerard Sanchez" wrote: > Hi, > > Can anyone help me on this: > ...

Need advice on Single Doc, Multi View framework
Hi guys, I'm about to start on a quoting application. What I want is for only one quote (document) to be open at a time. However a quote can have one or more items belonging to it. So what I'd like to do is have a CDialogBar docked at the side of my app's main window which contains the details about the quote (date, client, etc) and for each quote item I'd have a CFormView (a must as I require scrolling support). The application must also have print/print preview support (Not of what is in the CFormViews but of the document as a whole which doesn't have a view). So basical...

Help on subscript out of range error (VB6/VBA)
Hi, I want to work with multiple excel workbooks en worksheets using a stand alone VB6 application. 1. First I open and close the workbooks and populate a listbox with sheetnames. 2. Then I select some sheets and fill an Array. (This array contains sheetnames from different workbooks) 3. The last step is to open and close the workbooks again and loop (For Each) through the sheets to get the information I need using the above mentioned array. The only problem is that I receive an "Subscript out of Range" error. This error occurs because I'm attempting tot access a workshee...

HELP! **Transfer orders to invoices error
I need some serious help. We just upgraded from GP 7.5 to GP 8.0 and now I am unable to transfer orders to invoices. I keep getting an error that says "Order ORD1555 has no line items to transfer to Invoice INV144408." Order# 1555 does have about 10 line items, which I can view on the screen. I did go in and run the "Check Links" function, but it hasn't done anything. This is our busiest time of year and a really bad time for something like this to happen. Can someone please give me some advice on how to fix this? if this is just one order, why not cancel...

Any Help documentation for Exchange 2007?
E2k7' help button gives 'Cannot display Help' Technical details: HC not found. (MS.OSSADMIN.manifest , 1033) Did I miss something? Not all the help is completed in the Beta 2 release. "Prem" <premmetje@zonnet.nl> wrote in message news:1161634519.758601.285030@i42g2000cwa.googlegroups.com... > E2k7' help button gives 'Cannot display Help' Technical details: HC not > found. (MS.OSSADMIN.manifest , 1033) > Did I miss something? > ...

multiple colors on format of plot area
How can I change the format of the plot area to multiple colors. for example I would like to have a Bar Chart with the background plot area starting @ blue , then Green, then Red. Then have the temperature bars plot over this area. thanks You can do so by bringing a stacked column chart into the mix. Please see Jon's example below: http://peltiertech.com/Excel/Charts/ColoredQuadrantBackground.html -- John Mansfield http://cellmatrix.net "BillO" wrote: > How can I change the format of the plot area to multiple colors. > > for example I would like to have a ...

Pls, help a beginner(MFC)
Could anybody tell me what is the future of MFC? I never used that library. I just got some skills in pure C++. But some knowledgeable people are saying it's not a good idea to study MFC, because "it's going to be obsolete" and replaced by .Net. They advise me to study C# because as they say "studying MFC will take you quite a lot of time by which Longhorn will be released and all coding will be done in managed way"... Well, I really don't know which direction to elect According to me :) MFC never be obsolete and it would be safe...

how i can change default file format Outlook uses to save message
I my saving message in my inbox using "File->Save As" menu. "Save As.." dialog by default shows "Save As Type:" as "HTML". How I can change default file format Outlook uses to save messages to as "Outlook Message Format - Unicode" ?????? Rajkumar <Rajkumar@discussions.microsoft.com> wrote: > I my saving message in my inbox using "File->Save As" menu. > "Save As.." dialog by default shows "Save As Type:" as "HTML". > How I can change default file format Outlook uses to save messages...

Conditional Formatting with UsedRange Property for each Worksheet
I am getting an error indicated below in my code (Application-defined or object-defined Error, Run-Time Error '1004'). I want to have a bottome border line every 5 lines. Can someone explain to me why this portion of code will not work? Huge THANKS in advanced!!! Sub JobTracker1() Application.ScreenUpdating = False Call shUnProtect RowCount = 3 With Sheets("Archive") Do While .Range("L" & RowCount) <> "" myMonth = Format(.Range("L" & RowCount), "mmmm") Application.StatusBar = "Moving Sa...

Help
My Regional Manager insists on things being perfect - need assistance with this problem. Working in Governemtn department - we have many users with unusal names and surnames - not common to standard dictionaries. My Rm wants to forward emails received with these peoples names in the original message - without outlook spell checking each of these names before sending. I have set up the email options under spelling - to ignore original message when forwarding - which it does but still spell checks the original message's recipients before sending. How do I set this up to exclude the n...

Conditional Formatting #49
Does anyone know how to apply conditional formatting to a row of cells (i.e. to create a digital dashboard) based on the value of a particular cell? Let's say you want to change the formatting of row 2 if A1 is greater than 10: 1. Select row 2. 2. Format > Conditional Formatting 3. Formula Is and enter: =$A1>10 4. Format as desired. HTH Jason Atlanta, GA >-----Original Message----- >Does anyone know how to apply conditional formatting to a >row of cells (i.e. to create a digital dashboard) based on >the value of a particular cell? >. > ...

Distribution list help needed
We created a distribution list. Internal active directory users are able to send to the list. Internet e-mail senders are not able to send to the list. The mail is not bouncing back to the Internet e-mail users. The messages are just not getting to the inbox of the members of the distribution list. For example our domain is abc.net. The list we created is questions@abc.net. We have four users in the list. When a user logged into our Active Directory sends a mail to questions@abc.net the mail is successfully sent to the inboxes of all of the users in the list. When the e-mail ...

Formula Help #23
If I have a number in cell A1 like 5 and another number in cell A3 like 6 I want the number in A7 to show -1 and if cell A3 was 4 to read +1 and if the numbers are 2 away from A1 then the numbers in cell A7 would be -2 or +2 depending on the number in cell A3. All help is appreciated. Thanks Karl You must be kidding! =A1-A3 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Karl" <Karl@discussions.microsoft.com> wrote in message news:21FB7D9E-DC79-42...

Uploading Back to the Yahoo server...Please Help
Dear Member of this Help Forum; please help..please help me because i have lost all my important emails and i am so depressed...so sad....please Help....i HAVE SEARCHED THIS FROUM BUT NO ONE HAD THIS PROBLEM BEFORE.... My story and what i have done : I have a "Yahoo Small Business Account" with domain name and hosting, and 3 email address from that account. i used to use NO EMAIL CLIENT LIKE OUTLOOK at all and i used to save all my emails on yahoo servers and using yahoo normal email website to read, send and save all my emails...untill tonight! tonight i set up outlook 200...

How to add 6% to entire sheet?
I have a spreadsheet and I want to add 6% to whatever value is already in each cell. How does a guy do this? --- Message posted from http://www.ExcelForum.com/ Hi "skeetley" Make sure that you save a backup. To my mind it's probably best to copy your table of data to a new sheet. Put 1.06 in a cell Select that cell Copy Select the data to be uplifted Paste Special > Multiply > OK -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "s...

Help
Hi, i need to define a server side rule, which will copy attachments from received mails locally on PC/server. Any idea, scripts, anything? Tnx! Seems like ftp would be more appropriate than e-mail. But if you insist http://support.microsoft.com/search/default.aspx?spid=1773&query=event+sink+attachments&catalog=LCID%3D1033&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast=3&mode=a&x=18&y=11 HrVOjE wrote: > Hi, > > i need to define a server side rule, which will copy attachments from > received mails locally on PC/ser...

Conditional formatting assistance
Hey all, I was hoping one of you could point me in the right direction... I have a single sheet Excel document to which I want to apply some conditional formatting. Rows 1 and 2 are header information (date). Column 1 and 2 also display information that doesn't contain data (city). The sheet contains a list of broken items that I've fixed. When I've fixed the same thing twice, I want the cells containing that item to turn color. When it's entered a third time, turn a different color, and a fourth time, a final color. For example, I enter Chicago_washer_1 in cell C3. Thr...

how to setup quotionnaire format
please answer I'll take you mean questionaire == survey ? If so, have a look-see at Debra's page: http://www.contextures.com/xlForm01.html Excel -- Survey Form With Option Buttons (Techniques by Dave Peterson) There's 2 sample files you can download on the page -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Lalitbogra" wrote: > please answer Re: how to setup quotionnaire format ...

Re: Can anyone help me with the IF function
Hello I'm trying to help my wife with simplifying her scheduling o employees. She uses 11 different time periods (1-11) some are worth the same amount of hours others are more or less. I can us seven functions to describe the period and the value of hours it is worth, but when I consolidate the functions i says the formula is invalid. I'm sure it can be fixed by one of you excel gurus! Here are the functions IF(B13<4,"7.75") IF(B13=4,"7.25") IF(B13=5,"6.25") IF(B13=6,"7.75") IF(B13=7,"6.75") IF(B13=8,"6.25") IF(B...

Format DatePart
I'm trying to extract the month name (not just the number) from a ChkDate field in a query. ChkDate is a Date/Time data type. I use a DatePart expression which works fine to pull the month number. MonthNum: DatePart("m",[ChkDate]) But when I try to format that to get MonthName, it return Jan for any non- January month and Dec for any January month in ChkDate. Curious. Here's what I'm trying, and I can't see what's wrong according to syntax in help for Format and DatePart. Any ideas? MonthName: Format(DatePart("m",[ChkDate]),"mmm") Than...

.pst file is maxed out - HELP
Apparently I have reached the maximum size of my outlook.pst file (see attached error message). I have tried deleting items but nothing I do seems to have any effect and I continue to get the attached error message. Therefore the eMail part of Outlook is disabled. Anyone got any pointers? XP Pro SP-2/Outlook 2003. Already using .pst format for database. Thanks, No image attached. Post a link, rather than an attachment. See http://www.slipstick.com/problems/repair2gbpst.htm=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/o...

Need to Load Publisher
I have Publisher 98 CD and lost the jewel case that had the CD Key Value. I have anew computer and just needs the functionality of Publisher 98. How can I get the 11-disgit key value? I have a valid product ID when I loded the software on the old computer Help?? MIke http://www.phroogle.com/index.php?str=p&st=4520 -- Brian Kvalheim Microsoft Office Publisher MVP Official Publisher MVP Site: http://www.kvalheim.org This posting is provided "AS IS" with no warranties, and confers no rights. "Mike" <mbenya@adelphia.net> wrote in message news:027d01c3c...

Mirror a number
I am trying to mirror a number in a cell. For example, I want the product of 10 * 5 to equal 05 or at least be able to take a number like 1234 and change it to 4321 automatically. Please help me! Hi Steve try the following UDF (not much checking, etc. included): Function mirror(in_value) As Double Dim c Dim ret For c = Len(CStr(in_value)) To 1 Step -1 ret = ret & Mid(CStr(in_value), c, 1) Next mirror = CDbl(ret) End Function Use it like the following in your worksheet: =mirror(12345) or =mirror(A1) Note: mirror(5*10) would return '5' as the leading '0' is skipped ...

How to repalce numbers after a decimal
Lets say I have a column of numbers, that looks like this: 10.242 11.817 12.915 14.805 and I want it to look like this: 10.95 11.95 12.95 14.95 What function could be used to accomplish this? Format->Cells->Number tab->Click 'Custom', and enter this: 0.95 -- Regards, Dave "dpscoupe" wrote: > Lets say I have a column of numbers, that looks like this: > > 10.242 > 11.817 > 12.915 > 14.805 > > and I want it to look like this: > > 10.95 > 11.95 > 12.95 > 14.95 > > What function could be used to accomplish thi...

Convert number pad decimal to comma decimal separator
Hi, One of our european users has asked if we could add a feature to our app that would interpret a number pad decimal keystroke as a comma decimal separator. Whilst I could subclass the various number entry controls, dialog, grid, currency, plain number ect, I was hopeful that a application wide method could be found, utilising WM_KEYUP / WM_KEYDOWN. I have looked at using ProcessMessageFilter, but not had much success. Does anyone know how this may be acheived? Many thanks, Mike Stoodsmj wrote: > One of our european users has asked if we could add a feature to our > app that w...