Format data when combining in another cell

[Askjerry]

Here is an interesting one... I need to create a file name... it uses several fields to make one value.


Consider something like this...

Cell           Data               Info
--------------------------------------------------------------------------------------------------------------
[B]A1          [/B]    Widget          (Format: Text,  Item name, text)
[B]A2[/B]             0                         (Format: A number 0 to 999, the cell is formatted as "000" so it always shows 3 characters.)
[B]A3[/B]             B                          (Format: Text,   A product code, A through D)

So I should be able to make cell A4 into the file name like this...

In-Cell formula follows....

= A1 &"-" & A2 &"-" & A3

I would expect cell A4 to show : "Widget-000-B" but what I get is "Widget-0-B" which is incorrect.

So... instead of "A2" in the formula above... I need something to tell it the number should have three (3) characters...

How do I do that? (From in the cell... without VBA.)

Thanks,
Jerry

 

[AlphaFrog]

Try this...

= A1 & TEXT(A2,"-000-") & A3

 

[Askjerry]

Ah... that worked perfectly... there are some other uses I can see for that trick too... thanks!
(I'll take a closer look at the command to see what else it does.)