Get the language from a tag like en-US

Group en-US under en, so tags with the same language sort together.

What you'll make

In Excel: en-US becomes en.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8&"-"The tag with a hyphen added at the end."en-US-"
2TEXTBEFORE( … ,"-")Takes what comes before the first hyphen."en"
3LOWER( … )Makes the letters lowercase."en"

Each bracket wraps the one above it, so Excel works from the inside out. The added hyphen lets a tag like ja through.

=LOWER(TEXTBEFORE(A8&"-","-"))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own tags. Paste language tags into the green column of the Template tab. The main language fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This is for grouping only. Keep the full tag, like pt-BR, when choosing which version of a letter to send.

Microsoft's help: TEXTBEFORE function