Concatenate - combine numbers & text

		Microsoft Office Forums > Microsoft Excel > Excel
Concatenate - combine numbers & text

FAQ

Thread Tools

Display Modes

01-20-2020, 12:29 PM

NBVC

Windows 10 Concatenate - combine numbers & text

Office 2016

The Formula Guy

Join Date: Mar 2012

Location: Mississauga, CANADA

Posts: 215

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.

« Previous Thread | Next Thread »

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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:12 AM.