电子表格操作：如何将B列带前导0的9位ID拼接为邮箱用户名

Hey Stuart, let's get that email column set up exactly how you need it! The solution is straightforward, and works slightly differently depending on whether you're using Excel or Google Sheets—here's the breakdown for both:

1. First, make sure your B column IDs retain their leading zeros. If they're showing up as regular numbers (losing the starting 0), select the entire B column, right-click, choose Format Cells, then pick "Text" from the category list. This locks in the full 9-digit ID format.
2. Navigate to cell C2 (the first row where you need the email) and enter this formula:

   =B2&"@redcross.org.uk"
   

3. Hit Enter, and you’ll see the correct email populate in C2. To apply this to all rows, hover your cursor over the bottom-right corner of C2 until you see a small cross (the fill handle). Double-click it, or drag it down to cover every row with names/IDs.
4. Pro tip: If you can’t reformat the B column to text, use this formula instead to force the 9-digit format with leading zeros:

   =TEXT(B2,"000000000")&"@redcross.org.uk"
   

This is nearly identical to Excel, since the formula syntax matches:

1. Ensure the B column is set to "Plain text" (select B column → Format → Number → Plain text) to preserve leading zeros.
2. In cell C2, input the same core formula:

   =B2&"@redcross.org.uk"
   

3. Click the fill handle (bottom-right of C2) and drag down, or double-click to auto-fill all relevant rows.
4. If the B column is stored as numbers, use the TEXT formula trick here too to lock in the 9-digit format:

   =TEXT(B2,"000000000")&"@redcross.org.uk"
   

The best part? If you update any ID in column B later, the corresponding email in column C will automatically update to match—no manual edits required!

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