How can I add a symbol to an xlookup output

[Cypt2o]

Hello everyone I have the following formula:

XLOOKUP($M$5,'In Shop List'!C:C,'In Shop List'!A:A,"Update Inshop")

My result output is a number sequence show below.

202408100055

I would like to add a dash "-" inside the returned output. Example below:

20240810-0055

How would I go about doing this. Any is appreciated.

 

[Joe4]

Welcome to the Board!

Where it is returning the value from, are the entries made as Text or Numeric?
Due to the length, I am guessing that they are Text or else the value coming back would probably be in Scientific Notation.
Will it always return a 12 digit number, and you want to add the dash after the 8th character?

 

[Cypt2o]

It will always return a 12 digit number. The value return is text.

Yes. The dash after the 8th character.

 

[Fluff]

How about

=LET(x,XLOOKUP($M$5,'In Shop List'!C:C,'In Shop List'!A:A,"Update Inshop"),IF(x="Update Inshop",x,REPLACE(x,9,0,"-")))

 

[Joe4]

OK, try this formula:

=LET(x,XLOOKUP($M$5,'In Shop List'!C:C,'In Shop List'!A:A,"Update Inshop"),LEFT(x,8)&"-"&RIGHT(x,4))

 

[Fluff]

Joe, that will cause a problem if the xlookup returns "Update Inshop"

 

[Cypt2o]

Both solutions worked. Thank you both for your assistance.

 

[Joe4]

Joe, that will cause a problem if the xlookup returns "Update Inshop"

Ah, yes. I guess I should have analyzed the original formula more deeply to see exactly what it is doing in all situations.

Cypt2o,​

I would recommend using Fluff's solution, as it is more complete.