question about concatenating

[scotthannaford1973]

Hi folks
hope you can help here.

in C1 I have a cell value with a prefix added through the number format, so the number is 1, but the number format is: "PE"-2021-000 so that 1 shows as PE-2021-001

in D1 I have some text, say "Project name".

I want, in E1, to concatenate C1 and D1 to give me PE-2021-001 Project Name

Unfortunately, concatenating ignores the number format so I just end up with 1 Project Name

I have tried using TEXT and LEFT but cannot get the concatenated cell to show all of the contents of C1.

Help!

TIA

 

[LlebKcir]

Excel CELL function with formula examples

The tutorial shows how to use the CELL function in Excel to get various information about a cell such as cell address, contents, formatting, location, and more.

www.ablebits.com

that might help. I have not tried it, but the format arg should do what you are after without having to hand code the formatting. that would be doable, but might be more of a PITA than it would/could be worth.

 

[Scott T]

Number formatting is a façade and only changes how the number is displayed not the number. When you concatenate you are only acting on the number. You would need to concatenate PE-2021-00 along with project name.

="PE-2021-00"&C1&" "&D1

 

[CountTepes]

Put this into the cell

=TEXT(C1,"""PE""-2021-000") & " " & D1

 

[Fluff]

How about
="PE-2021-"&TEXT(C1,"000 ")&D1

 

[scotthannaford1973]

Thanks, Scott - that's okay when the number is 1, but (and I forgot to add!) below the 1 the numbers count up to 100, so I have PE-2021-001 down to PE-2021-100; using the standard formula you suggest, I'd get PE-2021-00100 rather than PE-2021-100

thanks, though - my mistake for not mentioning that.

Count Tepes - thanks, that works perfectly

 

[scotthannaford1973]

thanks, all - the Count's works perfectly.

 

[LlebKcir]

Yup Scott T has it correct, that was what I was attempting to avoid. Sadly after trying the link I provided, that is NOT the correct process. Sorry that only provides what the cell its, not the desired output of the cell formatting. le sigh.