Convert numbers to text to be combined in a From-To report heading

[johs]

I have a PQ table that contains two columns, "From" and "To," which have numerical values from 0 to 10 000 000. I would like the user to define intervals in an Excel sheet that should be used to group numbers in another table.

The input intervals could look like:

From: To:
0 100 000
100 001 1 000 000
1 000 001 5 000 000
5 000 001 10 000 000

I have no problems grouping the records correctly (I do that by comparing the numbers to be grouped with if statements and the "To" numbers and I put the "From" and "To" numbers in two columns that should be easy to combine in one column with "-" as a separator. But would like to output the result to Excel with a heading showing the intervals as above but where the separator "-" is always centered irrespective of the intervals the user is choosing. I would also like to format the numbers with a thousand separator.

I have tried to convert the numbers to text using the Text.Padstart function, but somehow 0 with 9 leading zeros is not the same length as 100 001 with 3 leading zeros. Not a big issue really, I just feel my output report look unprofessional with the "-" zigsagging in the output column. I would really like them to look like this:

0-100 000
100 001-1 000 000
1 000 001-5 000 000
5 000 001-10 000 000

Thank you for your time and assistance.

 

[Micron]

Maybe you posted with leading spaces or something? Based on the above I don't see how anything is centered as you describe. If that didn't come out right, I suggest you try reposting but put what you want within one of the code tag options from the posting toolbar. I take it that this is not the sort of centering you're after?

 

[johs]

Apologies and thank you for pointing out that yes I used leading spaces when writing the 2nd table, I didn't realize that leading spaces would be trimmed on posting. The centering i'm after is supposed to be in a vertical column and with the "-" centered -or in the same position- in all rows.

 

[Micron]

0 with 9 leading zeros is not the same length as 100 001 with 3 leading zeros

I don't think you can achieve exactly that unless you use a fixed width font.

 

[johs]

Should have thought about that, thank you!

 

[Micron]

You're welcome & thanks for the recognition!