I have a cell that contains a date formatted as 7/24/2007 we'll say cell A1

Another cell contains text and numbers at the end as BOTCM072407

I want the 2nd cell to be something like ="BOTCM" & "" & A1

The problem is when I do this it converts the date into numbers like 39286. I know where and why it converts to that number, but I don't want it to

Does anyone know how I would be able to get my result as BOTCM072407? Thanks!

 

if A1 is formated as text then

=BOTCM&A1 should help (it will leave your slashes in though)

There is excel function that removes specific characters from strings, exactly what the code is, I'm not entirely sure. Google knows I'm sure though...

 

best i can do

A1 = BCTOM
B1 = 7/24/2007

=A1&MONTH(B1)&DAY(B1)&YEAR(B1)

 

if you want leading zeros in the date you will need if()s

 

Wipe0ut I turned your code into..

="BOTCM"&"0"&MONTH($E$2)&DAY($E$2)&RIGHT(YEAR($E$2 ),2)

And it works.. thanks a lot

 

yeah "0"&MONTH($E$2) won't work for december

unless you want 012

if (month($E$2) > 9,MONTH($E$2), "0"&MONTH($E$2))

do the same thing for DAY()

 

IF A1 contains the date and A2 contains the text, this will work:

=CONCATENATE(A2,TEXT(A1,"mmddyy"))

 

 

Thanks khiyal.. I have to go back and redo my sheets though! +rep to you and - rep to wipeout for failing..



J/k wipeout