question about concatenating

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
63
Office Version
  1. 2010
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
206

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

Well-known Member
Joined
Dec 14, 2016
Messages
2,616
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
Code:
="PE-2021-00"&C1&" "&D1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,253
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

scotthannaford1973

Board Regular
Joined
Sep 27, 2017
Messages
63
Office Version
  1. 2010
Platform
  1. Windows
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
 

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
206
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,985
Messages
5,621,985
Members
415,872
Latest member
ReignEternal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top