Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2020, 10:43 AM
weety weety is offline Concatenate - combine numbers & text Windows 7 64bit Concatenate - combine numbers & text Office 2010 64bit
Novice
Concatenate - combine numbers & text
 
Join Date: Jun 2011
Posts: 12
weety is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 01-20-2020, 12:29 PM
NBVC's Avatar
NBVC NBVC is offline Concatenate - combine numbers & text Windows 10 Concatenate - combine numbers & text Office 2016
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 01-21-2020, 01:21 AM
weety weety is offline Concatenate - combine numbers & text Windows 7 64bit Concatenate - combine numbers & text Office 2010 64bit
Novice
Concatenate - combine numbers & text
 
Join Date: Jun 2011
Posts: 12
weety is on a distinguished road
Default

That's excellent. Works fine. Many thanks
Reply With Quote
  #4  
Old 01-21-2020, 03:28 AM
ArviLaanemets ArviLaanemets is offline Concatenate - combine numbers & text Windows 8 Concatenate - combine numbers & text Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

An example which also ensures that only an unique sequential document number may be entered.
Attached Files
File Type: xlsx DocumentsExample.xlsx (13.3 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate - combine numbers & text Concatenate function for displaying numbers in words officeboy09 Excel 13 08-09-2021 12:59 PM
Concatenate - combine numbers & text Concatenate text and a date Joanne Excel 6 11-15-2018 09:45 AM
Concatenate - combine numbers & text 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
Concatenate - combine numbers & text 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:22 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft