Leading Zero iwith Concat

[allieanne]

I've got two columns, department and position. I want to smush them together into one in order to use vlookup from another sheet.

The position number needs to be 3 digits long in the final product. I have 1,2, and 3 digit position numbers. I can of course make it show the leading zeros using formatting, but when I concat it drops the zeros. I need the result to be 111-001 not 111-1. There's all sorts of manipulations I could do to make this work, but wondering if there is a simple way that I'm overlooking?

 

[Joe4]

Use the TEXT function, i.e.

=TEXT(A1,"000") & "-" & TEXT(B1,"000")

 

[hotabae]

I would normally recommend formatting the position column.

Highlight the column, right click, "Format Cells..." , "Number Tab", Category = "Custom" , Type "000" (without quotes).

Dunno if there is an easier solution than that.

Edit: Doesn't meet requirements for Vlookup - See #4

 

[Joe4]

I would normally recommend formatting the position column.

Highlight the column, right click, "Format Cells..." , "Number Tab", Category = "Custom" , Type "000".

Formatting the column does not change the underlying value, just the way it is presented to the user.
In order for the VLOOKUP to work, you MUST update the underlying result, like I showed in the previous response.

 

[Joe4]

If you prefer to use CONCATENATE, you would do it like this:

=CONCATENATE(TEXT(A1,"000"),"-",TEXT(B1,"000"))

I prefer the first method I posted, as it is less typing!

 

[allieanne]

Thank you! I knew I was overlooking something ridiculously easy!