|
number vs text format
Hi jeffP,
jeffP wrote: I have to bring in a *dbf file, do some stuff, and then save it as a text file for a Unix system. I'm having a few problems (lack of VBA talent aside) but one I'm stuck on is I have two columns CUSTNUM AND INVNUM that import in a numeric format. CUSTNUM is anything from 1 to 4 digits. INVNUM IS 5 or 6 digits. I need to concatenate them to a 11 character format like this 0123-056788 (note leading zeros and hyphen). I have tried everything I can think of (programmatically or cell formatting) yet they remain underlying as a number and when I concatenate I lose the leading zeros so I'm stuck. I don't know if you're trying to do this after the import or during. If after, you can use a simple worksheet function to get the desired results: =TEXT(A1,"0000") & "-" & TEXT(B1,"000000") If you're doing this during the import using a SQL statement, you may be able to concatenate the 2 numeric fields into the desired format before bringing them in. If this is the case, supply your SQL statement and we'll try to help with it. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
number vs text format
Jake,
This has been a big help. Thanks. I was trying to do it programmatically because I need to learn. This worked great but, I have more. I actually have columns Cust,Inv,Date. The output cell I want is " Month End" <date , <cust-inv. You showed me the cust-inv concatenate. The "month end" constant I got in (if not very elegantly) by entering it a remote cell and referring to it (is there a better way?). My problem is that date is formatted as date (mm/dd/yy) in the original cell and when I refer to it , it comes in as the underlying numeric. I.e.: =Text(C1,"000000") shows 38232 instead of the desired 09/02/04. Any help is always appreciated. -- jeffP "Jake Marx" wrote in message ... Hi jeffP, jeffP wrote: I have to bring in a *dbf file, do some stuff, and then save it as a text file for a Unix system. I'm having a few problems (lack of VBA talent aside) but one I'm stuck on is I have two columns CUSTNUM AND INVNUM that import in a numeric format. CUSTNUM is anything from 1 to 4 digits. INVNUM IS 5 or 6 digits. I need to concatenate them to a 11 character format like this 0123-056788 (note leading zeros and hyphen). I have tried everything I can think of (programmatically or cell formatting) yet they remain underlying as a number and when I concatenate I lose the leading zeros so I'm stuck. I don't know if you're trying to do this after the import or during. If after, you can use a simple worksheet function to get the desired results: =TEXT(A1,"0000") & "-" & TEXT(B1,"000000") If you're doing this during the import using a SQL statement, you may be able to concatenate the 2 numeric fields into the desired format before bringing them in. If this is the case, supply your SQL statement and we'll try to help with it. -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
number vs text format
Hello!
For date try =TEXT(C1;"MM/DD/YY") Maybe that helps. Ro -- Message posted from http://www.ExcelForum.com |
number vs text format
Hi jeffP,
jeffP wrote: I actually have columns Cust,Inv,Date. The output cell I want is " Month End" <date , <cust-inv. You showed me the cust-inv concatenate. The "month end" constant I got in (if not very elegantly) by entering it a remote cell and referring to it (is there a better way?). My problem is that date is formatted as date (mm/dd/yy) in the original cell and when I refer to it , it comes in as the underlying numeric. I.e.: =Text(C1,"000000") shows 38232 instead of the desired 09/02/04. If you want the month end date for the *current* month, you could use something like this in your formula: =TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"mm/dd/yy") If you want a different month end, you could substitute the date for each occurence of TODAY(). -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
number vs text format
Jake,
Thanks for you help. I got this to work fine. I have more problems with this project that I'm going to post but this is great. Here's how it ending up looking. =TEXT("Month End","@")&" "&TEXT(A1,"mm/dd/yy")&" "&TEXT(A2,"000000")&" - "&TEXT(A3,"000000") Output looks like this. Month End 08/31/00 000241 - 208003 I didn't use your formula of Date because the user is selecting the date from the datalist. Thanks again. -- jeffP "Jake Marx" wrote in message ... Hi jeffP, jeffP wrote: I actually have columns Cust,Inv,Date. The output cell I want is " Month End" <date , <cust-inv. You showed me the cust-inv concatenate. The "month end" constant I got in (if not very elegantly) by entering it a remote cell and referring to it (is there a better way?). My problem is that date is formatted as date (mm/dd/yy) in the original cell and when I refer to it , it comes in as the underlying numeric. I.e.: =Text(C1,"000000") shows 38232 instead of the desired 09/02/04. If you want the month end date for the *current* month, you could use something like this in your formula: =TEXT(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),"mm/dd/yy") If you want a different month end, you could substitute the date for each occurence of TODAY(). -- Regards, Jake Marx MS MVP - Excel www.longhead.com [please keep replies in the newsgroup - email address unmonitored] |
| All times are GMT +1. The time now is 08:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com