Extracting just the numbers from auto generated Created field

Trang Turtletraxx

I know how to extract just the time or just the date from auto generated Created and Modified fields. I also know how to remove the : and PM/AM from the time and / from the date. All this done individually.

My question - is there a simple formula to extract JUST the numbers from the auto generated field? Or can someone help me create one? I've tried to combine all the individual fields identified above and it's not working.

Thank you!

Emory

=SUBSTITUTE(MID([Column8]@row, 1, 2) + MID([Column8]@row, 4, 2) + MID([Column8]@row, 7, 2) + MID([Column8]@row, 9, 2) + MID([Column8]@row, 12, 2), " ", 0)

I think this is a good work around for what you want.

I wrote this formula to replace the space with "0". This is because if it is 12:55PM for example, you will want 1255. But if it is 5:55PM, it would return " 555" (with a space)

If you would prefer that the space is not replaced with a 0, you can adjust the substitution formula to reflect that:

=SUBSTITUTE(MID([Column8]@row, 1, 2) + MID([Column8]@row, 4, 2) + MID([Column8]@row, 7, 2) + MID([Column8]@row, 9, 2) + MID([Column8]@row, 12, 2), " ", "")

I hope this workaround helps. It isn't a simple fix, but it seems to work from what I have tested.

Emory

You may also consider a nested substitution function:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Column8]@row, "/", ""), ":", ""), " ", ""), "P", ""), "A", ""), "M", "")

You could use this to remove any characters that are not desired. Personally, I like the substitution/MID function option better though.

Emory

If you can do them individually, you may be able to use the Join function to reconnect the parts into one cell.

I took a brief attempt and got something like this =JOIN(Month@row:TIME@row)

Would this work for what you need?

Trang Turtletraxx

I would still have to do the individual breakouts of the date and time before getting to your JOIN formula for the output. My question and ask is if there is (or if someone can help me come up with) a formula that skips having to breakout anything part of the auto generated cell and extract just the numbers (i.e. remove / in the date and : PM/AM in the time). If we take your screen shot above, I'd want to take Column 5 and get to your JOIN column without the columns between the two (minus the semicolon in your solution)

Auto generated cell = 02/13/24 5:04 PM

Output from formula = 21324505

Hope that makes more sense. Thanks for the attempt.

Emory

=SUBSTITUTE(MID([Column8]@row, 1, 2) + MID([Column8]@row, 4, 2) + MID([Column8]@row, 7, 2) + MID([Column8]@row, 9, 2) + MID([Column8]@row, 12, 2), " ", 0)

I think this is a good work around for what you want.

I wrote this formula to replace the space with "0". This is because if it is 12:55PM for example, you will want 1255. But if it is 5:55PM, it would return " 555" (with a space)

If you would prefer that the space is not replaced with a 0, you can adjust the substitution formula to reflect that:

=SUBSTITUTE(MID([Column8]@row, 1, 2) + MID([Column8]@row, 4, 2) + MID([Column8]@row, 7, 2) + MID([Column8]@row, 9, 2) + MID([Column8]@row, 12, 2), " ", "")

I hope this workaround helps. It isn't a simple fix, but it seems to work from what I have tested.

Emory

You may also consider a nested substitution function:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Column8]@row, "/", ""), ":", ""), " ", ""), "P", ""), "A", ""), "M", "")

You could use this to remove any characters that are not desired. Personally, I like the substitution/MID function option better though.

Trang Turtletraxx

@Emory your recent solutions are PERFECT! Thank you so very much!