#1
|
|||
|
|||
Concatenate - combine numbers & text
I'm using a spreadsheet to create unique document numbers. The document numbers are built up from various elements as follows.
Document type - a 2 character text entry e.g. BR Document number - a unique and sequential number formatted to return as 6 digits e.g. 000001 Document language - a 4 char text entry e.g. ENGB A date formatted as mm-yy a revision number When I use concatenate to bring all these together the formula converts the unique number to 1 and not 000001 and converts the date to a number. So what I get in this case is BR1ENGB-1_43800 instead of BR000001ENGB-1_12-19 I could enter the number and date fields as text which would return the right number but this adds a layer of complication for those using the spreadsheet. Any ideas? |
#2
|
||||
|
||||
To format numbers and dates into text strings like that you need to use the TEXT function.
e.g. ="BR"&TEXT(A2,"000000")&"ENGB"&SUBSTITUTE(TEXT(B2, "mm-yy"),"-","_")&"1" Where A1 contains a number and B2 contains a date. You can replace any text in quotes with cell references. If you want an underscore instead of dash to separate month and year, you have to use the SUBSTITUTE function to replace the dash. |
#3
|
|||
|
|||
That's excellent. Works fine. Many thanks
|
#4
|
|||
|
|||
An example which also ensures that only an unique sequential document number may be entered.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Concatenate function for displaying numbers in words | officeboy09 | Excel | 13 | 08-09-2021 12:59 PM |
Concatenate text and a date | Joanne | Excel | 6 | 11-15-2018 09:45 AM |
Concatenate | b16 | Excel | 1 | 01-09-2017 04:51 PM |
how to concatenate text box on new slide | wichitawx | PowerPoint | 5 | 05-19-2015 05:17 AM |
How to combine many word files in one file but to have correct pages numbers and tabl | Jamal NUMAN | Word | 6 | 04-20-2011 02:32 PM |