number vs text format

[jeffP]

HI all,
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.

Any help and direction is always appreciated.

 

[Jake Marx]

Hi jeffP,

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]

 

[jeffP]

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
(e-mail address removed)

Hi jeffP,

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]

 

[Roderick]

Hello!

For date try =TEXT(C1;"MM/DD/YY")

Maybe that helps.

Ro

 

[Jake Marx]

Hi jeffP,

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]

 

[jeffP]

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
(e-mail address removed)

Hi jeffP,

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]