User-defined number format to truncate decimal places

[lphilpot]

LibreOffice 7.6.2.1 on Windows 11

I have a couple of columns full of numbers like this (celestial coordinates, if you’re curious):

02 58 15.6752536
01 37 42.84548
00 49 06.2945942922
16 05 26.23
08 58 29.2042107048
10 16 41.4159679
06 58 37.54876

What I want to do is simply truncate the final group down to two digits, i.e.:

02 58 15.67
01 37 42.84
00 49 06.29
16 05 26.23
08 58 29.20
10 16 41.41
06 58 37.54

I’ve tried a user-defined format like ## ## ##.## but that makes no difference. I read through the associated help and tried various things (and searched online as well) but to no avail. I’m sure it’s simply a matter of misunderstanding how to define formats.

Can anyone give me a push in the right direction?

Thanks!

[mikekaganski]

If you had numbers, they would change with the number format you tried; note that e.g.

@lphilpot

06 58 37.54876

would become

@lphilpot

06 58 37.55

not

@lphilpot

06 58 37.54

(possibly you could need to use " " in double quotes, instead of simple spaces, if spaces are thousand separators in your locale).

But likely you have text, not numbers (check using View → Value Highlighting Ctrl+F8); and what could possibly help you might be Data → Text to Columns, and choose Fixed width, adjust the first column to 11 characters, then drop the second resulting column with remainders.

[JohnSUN]

May be so?

Function truncSeconds(aSourceValue As String, Optional countDigits As Integer) As String
Dim aParts() As String, sFormat As String 
	If IsMissing(countDigits) Then countDigits = 2
	sFormat = Left("00.0000000000000000", countDigits+3)
	aParts = Split(aSourceValue, " ")
	truncSeconds = aParts(0) & "°" & aParts(1) & "'" &  Format(Val(aParts(2)), sFormat) & Chr(34)
End Function

Most likely, to further work with these values, you will need to obtain these coordinates in the form of angular measurements in degrees and decimals of degrees? The Convert_Decimal() function will help you

[karolus]

In case this Data is degree minutes seconds ?
you can search and replace the spaces with : and apply Numberformat-code:
[HH]° MM′ SS.SS″
to convert and calculate with their Decimal-Degree-equivalent you need just multiply by 24

In the screenshot the raw Text is in Column A
Column B shows normal Time-durations after Search&Replace.
Column C shows after applying the Number-Format-Code above.

[Wanderer]

@lphilpot

I’m sure it’s simply a matter of misunderstanding how to define formats

No, like Mike my guess is, you don’t have numbers. Your human brain translates the contents to numbers. Calc on the other side will detect a space-character between numbers and treat the value as text. And formatting neither changes the type nor the value in the cell.
.
You may show 0.5 as 12:00:00 with formatting, but the cell is not changed. Also formatting for numbers does not apply to text.
.
In your example on could try to truncate the string to the LEFT 11 chars with a function to display only that part.

[lphilpot]

@mikekaganski

But likely you have text, not numbers

Ctrl+F8 shows no change, but this seems to indicate they’re classified as numbers. I’ve tried copying the range out to a text editor, copying and pasting back just to remove any chance of hidden characters, formatting, etc. But it still doesn’t work.

[lphilpot]

@karolus

In case this Data is degree minutes seconds ?
you can search and replace the spaces with : and apply Numberformat-code:
[HH]° MM′ SS.SS″

Actually this is hours of right ascension, but there’s a declination column in dd mm ss.ssssss format that needs the same treatment. I’ll give this a shot as it looks to be closest to what I need.

Thanks!

[lphilpot]

@Wanderer

Calc on the other side will detect a space-character between numbers and treat the value as text. And formatting neither changes the type nor the value in the cell.

That appears most likely to me.

Thanks.

[lphilpot]

@karolus

search and replace the spaces with :

As it turns out this was entirely sufficient by itself:

(217 rows in total)

Thanks!

[mikekaganski]

@lphilpot

Ctrl+F8 shows no change, but this seems to indicate they’re classified as numbers

Lol. Did you try a simple test like “enter number 1 to A1, and check what Ctrl+F8 does”?

@lphilpot

As it turns out this was entirely sufficient by itself

Note that this changed your text to numbers (namely, times). And that didn’t remove the rest of digits - only hidden them

[lphilpot]

@mikekaganski

Lol. Did you try a simple test like “enter number 1 to A1, and check what Ctrl+F8 does”?

Yes. A1 already has other content so I used a different cell. Ctrl+F8 hid the contents of A1:

Normal:

Ctrl+F8:

@mikekaganski

Note that this changed your text to numbers (namely, times). And that didn’t remove the rest of digits - only hidden them

Yep, understood but it’s irrelevant here. Printed output is all that matters.