如何实现数据集时分列拼接并为小于10的分钟补前导零？

Hey there! Let's get that formula working properly for you. Your goal is to combine column A (hours) and column B (minutes) into a formatted string like 1.02—with a leading zero for single-digit minutes—and your initial formula had a couple of small issues preventing it from running. Here's the breakdown:

What Was Wrong With Your Original Formula? #

• Function Name Typo: You wrote Con instead of the correct Excel function name CONCAT (or CONCATENATE for older Excel versions). While Excel is case-insensitive, spelling matters here.
• Unreliable Length Check: Using Len(B) directly on a numeric cell can cause unexpected behavior (e.g., if the cell has hidden formatting). It's better to format the minute value explicitly instead of checking its length.

Recommended Solution (Cleanest & Most Reliable) #

Use the TEXT function to automatically format minutes with a leading zero, then concatenate everything together. This avoids messy conditional checks entirely:

=CONCAT(A1,".",TEXT(B1,"00"))

How It Works:

• TEXT(B1,"00"): Formats the minute value to always be two digits. If B1 is 2, it becomes "02"; if B1 is 15, it stays "15".
• CONCAT(A1,".",...): Joins the hour value, a dot, and the formatted minute string into your desired output.

Alternative: Fixed Version of Your Original Approach #

If you prefer to stick with the conditional logic you started with, here's the corrected formula (we convert the minute value to text first to avoid length check issues):

=CONCAT(A1,".",IF(LEN(TEXT(B1,"#"))=1,CONCAT("0",B1),B1))

For Older Excel Versions (Pre-2019) #

If your Excel doesn't support CONCAT, use CONCATENATE instead:

=CONCATENATE(A1,".",TEXT(B1,"00"))

Test Example #

• Input: A1=1, B1=2
• Output: 1.02 (matches your desired result perfectly!)

内容的提问来源于stack exchange，提问作者Kelly