I need an excel formula that will extract the last four digits of a cell A2 and combine them with the first two words of the cell in B2, but the data from the two cells should be put together with a hyphen. The space between the words in the second cell should be maintained.
user
Sure, you can use the following formula to extract the appropriate values from the selected cells and concatenate them with a hyphen and the specified spaces:

```
=TEXTJOIN(" - ",TRUE,LEFT(B2,FIND(" ",B2,1),2)&" "&RIGHT(A2,4))
```

Breakdown:

1. `LEFT(B2,FIND(" ",B2,1),2)` will extract the first two words from the `B2` cell.
2. `" "` is the space character we want to insert between the two strings we'll concatenate.
3. `TEXTJOIN(" - ",TRUE,"...")` is the concatenation function. It concatenates all the inputs separated by the specified delimiter. The `TRUE` argument is important when you want to concatenate a range of cells like this.
4. `" - "` is the separator that will join the strings we are concatenating.
5. `...` is the range of cells that we are concatenating. This is where the concatenated string would go if there were more than one cell.

So, in our case, `...` would be replaced by:

```
LEFT(B2,FIND(" ",B2,1),2)&" "&RIGHT(A2,4)
```

This is essentially what this part does. It concatenates `" "` after the two words you get from `LEFT()`, followed by the last four digits you get from `RIGHT()`. I hope this helps!
assistant
I need an excel formula that will extract the last four digits of a cell A2 and combine them with the first two words of the cell in B2, but the data from the two cells should be put together with a hyphen. The space between the words in the second cell should be maintained.
user
You can use the following formula to achieve the desired result:

```
=TEXTJOIN(" ",TRUE,LEFT(TRIM(MID(SUBSTITUTE(B2," ",REPT(" ",99)),ROW($1:$99)*99-98,99)),2)) & " - " & RIGHT(A2,4)
```

Here's a breakdown of the formula:

1. `SUBSTITUTE(B2, " ", REPT(" ", 99))` replaces all spaces in B2 with 99 spaces.
2. `MID(<result from step 1>, ROW($1:$99)*99-98, 99)` separates the text into individual words.
3. `TRIM(<result from step 2>)` removes excess spaces.
4. `LEFT(<result from step 3>, 2)` extracts the first two words.
5. `TEXTJOIN(" ", TRUE, <result from step 4>)` combines those words with a space in between.
6. `RIGHT(A2, 4)` extracts the last four digits from A2.
7. Finally, `<result from step 5> & " - " & <result from step 6>` concatenates the extracted words and digits with a hyphen in between.
assistant