Community
Inventor Forum
Welcome to Autodesk’s Inventor Forums. Share your knowledge, ask questions, and explore popular Inventor topics.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

One for the Excel gurus

8 REPLIES 8
Back to Inventor Category
Back to Topic Listing
Reply
Message 1 of 9
Anonymous
216 Views, 8 Replies
‎10-09-2003 11:18 PM
‎10-09-2003 11:18 PM

One for the Excel gurus

I am working on setting up an iPart. The part number description is a series
of numbers designating sixteenths of an inch. For example a 1/2 IDx5/8ODx1
LG part becomes 081016. The question is how do I get the preceding zeros. I
can use a custom format to get a collum with the correct format: 2 digits
with a preceding zero if necessary. But when I use concatenate it reverts
the custom format back to general so that 08 becomes 8. Is there a way to do
this?

TIA

keith
Report
0 Likes
Reply
8 REPLIES 8
Message 2 of 9
Anonymous
in reply to: Anonymous
‎10-09-2003 11:30 PM
‎10-09-2003 11:30 PM

How are your cells formatted? Are they set to a number format, general, or
text. If they are set to a text format, you shouldn't have a problem
keeping the preceding zeros.

Blane
Report
0 Likes
Reply
Message 3 of 9
Anonymous
in reply to: Anonymous
‎10-09-2003 11:37 PM
‎10-09-2003 11:37 PM

the cells that I have the individual numbers i.e. 08 10 16 are custom
format in order to get the preceding zero (when necessary). The cells that
have the actual P/N is a text cell but still will not carry the preceding
zeros from the custom cells.




"BTBeilke" wrote in message
news:601A81B07CA3CE2BFF9E7D4A2FC65298@in.WebX.maYIadrTaRb...
> How are your cells formatted? Are they set to a number format, general,
or
> text. If they are set to a text format, you shouldn't have a problem
> keeping the preceding zeros.
>
> Blane
>
>
Report
0 Likes
Reply
Message 4 of 9
dtwist
Enthusiast
in reply to: Anonymous
‎10-09-2003 11:55 PM
‎10-09-2003 11:55 PM

Us something like this in you concatenation cell ... =TEXT(A4,"00")&TEXT(B4,"00")&TEXT(C4,"00")
Report
0 Likes
Reply
Message 5 of 9
Anonymous
in reply to: Anonymous
‎10-10-2003 12:17 AM
‎10-10-2003 12:17 AM

The way I understand what you are doing is that you are creating 3 columns
with the correct 2-digit format for each number. These columns have a
custom format with the type "00" so that leading zeros are displayed. When
you concatenate those cells, the leading zeros are dropped. How is the data
getting into the 3 columns? Are you using a formula based on the original
part description?

What you could do is ignore the formatting of your 3 columns and just put
the numbers you need. For example, if I enter the number 1 into cell A1, 2
into cell B1, and 3 into cell C1 (all without displaying leading zeros), I
can concatenate them with this formula:

=CONCATENATE(TEXT(A1,"00"),TEXT(B1,"00"),TEXT(C1,"00"))

and get "010203" as a result.

I hope this helps.

Blane
Report
0 Likes
Reply
Message 6 of 9
Anonymous
in reply to: Anonymous
‎10-10-2003 12:20 AM
‎10-10-2003 12:20 AM

Good idea!  You beat me to it.  I got
interrupted by a phone call (should I be working at work?) and didn't check for
responses before I posted.  Anyway, GMTA.

 

Blane


style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Us
something like this in you concatenation cell ...
=TEXT(A4,"00")&TEXT(B4,"00")&TEXT(C4,"00")
Report
0 Likes
Reply
Message 7 of 9
Anonymous
in reply to: Anonymous
‎10-10-2003 12:23 AM
‎10-10-2003 12:23 AM

No, that doesn't work because it needs to put a
preceding zero only if it is a singlt digit, double digit numbers stay as they
are.

 

Thanks anyway

kp

 

 


style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
Us
something like this in you concatenation cell ...
=TEXT(A4,"00")&TEXT(B4,"00")&TEXT(C4,"00")
Report
0 Likes
Reply
Message 8 of 9
Anonymous
in reply to: Anonymous
‎10-10-2003 12:30 AM
‎10-10-2003 12:30 AM

In my example/spreadsheet, if I enter 1, 22, and 3
into cells A1, B1, and C1, the formula I suggested gives me a result of
012203.  It doesn't matter if the number in the cells is one or two
digits.  Only the result is forced to two digits.  Isn't that how you
want it to work.  Maybe if you posted your spreadsheet in ICF, we could
take a look at it.

 

Blane
Report
0 Likes
Reply
Message 9 of 9
Anonymous
in reply to: Anonymous
‎10-10-2003 12:46 AM
‎10-10-2003 12:46 AM

Yep, That works, It just took a couple of blows (to the head) before I
could get it to sink in.

THANKS!
keith



"BTBeilke" wrote in message
news:3E1CD944CB856087C4007DD861CD471F@in.WebX.maYIadrTaRb...
> The way I understand what you are doing is that you are creating 3 columns
> with the correct 2-digit format for each number. These columns have a
> custom format with the type "00" so that leading zeros are displayed.
When
> you concatenate those cells, the leading zeros are dropped. How is the
data
> getting into the 3 columns? Are you using a formula based on the original
> part description?
>
> What you could do is ignore the formatting of your 3 columns and just put
> the numbers you need. For example, if I enter the number 1 into cell A1,
2
> into cell B1, and 3 into cell C1 (all without displaying leading zeros), I
> can concatenate them with this formula:
>
> =CONCATENATE(TEXT(A1,"00"),TEXT(B1,"00"),TEXT(C1,"00"))
>
> and get "010203" as a result.
>
> I hope this helps.
>
> Blane
>
>
Report
0 Likes
Reply
Reply

Can't find what you're looking for? Ask the community or share your knowledge.

Post to forums  

Autodesk Design & Make Report

Report a website issue