如何用Excel的MID、RIGHT和FIND函数提取组合州与邮编？

To combine the state (NSW) and postcode (2007) from Inventory!F4 into "NSW2007" using MID and FIND, use this formula:

=MID(Inventory!F4, FIND(",", Inventory!F4, FIND(",", Inventory!F4)+1)+2, 3) & MID(Inventory!F4, FIND(",", Inventory!F4)+2, 4)

Formula Breakdown: #

• Extract Postcode (2007):

  • FIND(",", Inventory!F4) locates the first comma in the cell content.
  • +2 skips the comma and following space to position at the start of the postcode.
  • MID(..., 4) grabs the next 4 characters to get "2007".

• Extract State (NSW):

  • FIND(",", Inventory!F4, FIND(",", Inventory!F4)+1) finds the second comma (starting the search after the first comma).
  • +2 skips this comma and following space to position at the start of the state code.
  • MID(..., 3) grabs the next 3 characters to get "NSW".

• Combine Results:

  • The & operator concatenates the extracted state and postcode into "NSW2007".

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