concatenate formula with text and date is returning wrong format

[thewiseguy]

Hi All, here is my current formula:

=CONCATENATE(B5," ",F5)

B5 = 2024-08-29
F5 = Cedarcrest

Output = 45533 Cedacrest

I need this to say "20240829-Cedarcrest"

Can anyone help with these adjustments?

 

[Scott Huish]

=TEXT(B5,"yyyymmdd")&"-" & F5

 

[thewiseguy]

=TEXT(B5,"yyyymmdd")&"-" & F5

excellent thank you! So "Cedacrest" is actually "Cedarcrest Property Management"

1 other request.....is it possible to remove the spaces in B5, so that the output it:

20240829-CedacrestPropertyManagement

 

[Asbestos_Jen]

You can use the TRIM() function to remove spaces from text:

=TEXT(B5,"yyyymmdd")&"-" & trim(F5)

 

[thewiseguy]

You can use the TRIM() function to remove spaces from text:

=TEXT(B5,"yyyymmdd")&"-" & trim(F5)

hi - for some reason that is not removing the spaces?

 

[Asbestos_Jen]

The spaces may be other non-printable characters that just look like spaces. Does

=SEARCH(" ",F5)

return a number?

 

[thewiseguy]

The spaces may be other non-printable characters that just look like spaces. Does

=SEARCH(" ",F5)

return a number?

yes, 20

 

[Asbestos_Jen]

What does =TRIM(F5) return?

 

[Scott Huish]

TRIM isn't the function you need, SUBSTITUTE is:
=TEXT(B5,"yyyymmdd")&"-" & SUBSTITUTE(F5," ","")

TRIM doesn't remove all spaces, it removes leading and trailing spaces as well as extra ones in the text.

 

[thewiseguy]

thank you!