1

I want to change a list of strings by keeping only the beginning and end of the strings and removing the middle.

I want to keep:

  1. first name + space
    first word of the string
  2. last initial + space
    first letter of the second word
  3. - + reason for the absence
    the dash and everything that follows it

I have managed to get part-way and have tried TRIM and REGEXREPLACE but need help getting the desired output with a formula.

Please see below. My initial strings are in column A and the outcome I want is in column 2.

enter image description here

1 Answer 1

0

Formula in B2

  =LEFT(A2, FIND(" ",A2)+1)&" "&
   RIGHT(A2,LEN(A2)-FIND("-",A2)+1)

Sample Output

  |--------------------------------------------------------|--------------------------|
1 | INPUT                                                  | OUTPUT                   |
  |--------------------------------------------------------|--------------------------|
2 | Alex Hill will be 1 day on a leave - Holidays          | Alex H - Holidays        |
  |--------------------------------------------------------|--------------------------|
3 | Anna Low will be 273 days on a leave - Maternity leave | Anna L - Maternity leave |
  |--------------------------------------------------------|--------------------------|
4 | Frank Tibbs will be 2 days on a leave - Holidays       | Frank T - Holidays       |
  |--------------------------------------------------------|--------------------------|
5 | Matthew Small will be 4 days on a leave - Holidays     | Matthew S - Holidays     |
  |--------------------------------------------------------|--------------------------|
6 | Simon Wright will be 5 days on a leave - Holidays      | Simon W - Holidays       |
  |--------------------------------------------------------|--------------------------|
1
  • I have another Q (sorry) If the above 5 lines of data were in 1 cell (with line breaks).... Is it possible to put a funky formula into B2 so that the output will be same (as in 5 lines in 1 cell)?
    – HFE2022
    Dec 7, 2022 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.