2

I have am using a formula that extracts part of a text string from a cell in Google Sheets based on this answer to an earlier question.

The formula is =LEFT(A2, FIND(" ", A2)+1)&" "&RIGHT(A2,LEN(A2)-FIND("-",A2)+1) where A2 is a reference to my source text.

Example Output with formula in B2

  A B
2 Alex Hill will be 1 day on a leave - Holidays Alex H - Holidays

This works when each string is in a separate row, but I have a number of lines of data in the same cell and the formula will only work on the the first line of text.

Example with same formula in B2 and a multi-line string in A2

  A B
2 Alex Hill will be 1 day on a leave - Holidays
Frank Tibbs will be 2 days on a leave - Holidays
Matthew Small will be 4 days on a leave - Holidays
Simon Wright will be 5 days on a leave - Holidays
Anna Low will be 273 days on a leave - Maternity leave
Alex H - Holidays

Is there a similar formula that would do the same job but for each and every line in a multi-line cell with the the formula result also being a multi-line cell?

This is an example of what I want to see in B2 based on a multi-line string in A2

  A B
2 Alex Hill will be 1 day on a leave - Holidays
Frank Tibbs will be 2 days on a leave - Holidays
Matthew Small will be 4 days on a leave - Holidays
Simon Wright will be 5 days on a leave - Holidays
Anna Low will be 273 days on a leave - Maternity leave
Alex H - Holidays
Frank T - Holidays
Matthew S - Holidays
Simon W - Holidays
Anna L - Maternity leave
0

2 Answers 2

1

If A1 contains the text, this may be a solution:

=join(char(10),byrow(transpose(split(A1,char(10))),lambda(r,left(r,find(" ",r)+1)&right(r,len(r)-find("-",r)+2))))

Link to a test file

1

Your original question had a single line of data in each row and you were seeking a single line of data in the formula result. Your new question has multiple lines of data joined in a single cell with a line break char(10) as delimiter and you'd like the output to have a similar format.

In order to adapt the original formula, you must first change the source data from multiple lines in one row, to a single line in multiple rows then after applying the formula to each row in turn, you will need to change the resulting output from multiple single line rows back into a single row with multiple lines.

@Daniele correctly adapted my formula for your new use case first and should be credited with the answer.

Understanding the steps will better allow you to adapt them to other formulas.

  1. Split the Data into multiple rows at the line break character [char(10)].
    =SPLIT(text, delimiter, [split_by_each], [remove_empty_text]))
  =SPLIT(A1,char(10))
  1. Transpose the resulting array of columns into an array of rows.
    Following this step, your data layout will now mimic the layout of the data in your original question.
    =TRANSPOSE(array_or_range)
  =TRANSPOSE(SPLIT(A1,char(10)))
  1. Convert the original formula into a LAMBDA function
    =LAMBDA([name, …], formula_expression)
  =LEFT(A1, FIND(" ", A1)+1)&" "& 
        RIGHT(A1, LEN(A1)-FIND("-", A1)+1)

  // becomes 

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

  //  `x` as `name` is an arbitrary choice, you can
  //  replace by a a different letter or word.
  1. Use BYROW to apply the LAMBDA function to the array of rows created in #2 above.
    Following this step, the layout of the formula result will now mimic the output layout in your original question.
    =BYROW(array_or_range, lambda)
  =BYROW( TRANSPOSE(SPLIT(A1,char(10))), 
          LAMBDA(x, LEFT(x, FIND(" ", x)+1)& " "& 
                          RIGHT(x, LEN(x)-FIND("-", x)+1))) 
  1. Finally, JOIN the resulting array from #4 above into a single cell using the line break character as delimiter.
    =JOIN(delimiter, value_or_array1, [value_or_array2, …])
  =JOIN(char(10), BYROW(TRANSPOSE(SPLIT(A1,char(10))),
                         LAMBDA(x, LEFT(x, FIND(" ", x)+1)& " "&
                                RIGHT(x, LEN(x)-FIND("-", x)+1))))

  // or simply

  =JOIN(char(10), BYROW(TRANSPOSE(SPLIT(A1,char(10))), LAMBDA(x, LEFT(x, FIND(" ", x)+1)& " "&RIGHT(x, LEN(x)-FIND("-", x)+1))))

Variations

The steps above assume that your array is 1x1 (1 Row x 1 Col). There is no reason the array couldn't be 3x1, 100x1, or (n)x1 if you adapt the step #1 to first JOIN the range with a line break delimiter. This would work as well for 1x1 as for (n)x1 So, if useful,

  1. (new #1) JOIN all rows in range delimited by line break, then SPLIT the data into rows at the line break character [char(10)].
  // Instead of 
  
  =SPLIT(A1,char(10))

  // use
  =SPLIT(JOIN(char(10), A1), char(10))

  // which changes the final formula to

  =JOIN(char(10), BYROW(TRANSPOSE(SPLIT(JOIN(char(10), A1), char(10))),
                         LAMBDA(x, LEFT(x, FIND(" ", x)+1)& " "&
                                RIGHT(x, LEN(x)-FIND("-", x)+1))))

  // In this variation, A1 could also be A1:A10 or A:A
  1. (skip step #4) As mentioned in step #4, if you stopped there, regardless if your data was a line per row or multiple lines in one or more rows, you would at this point have output that gave you one line per row.
 =BYROW(TRANSPOSE(SPLIT(JOIN(char(10), A1), char(10))),
                         LAMBDA(x, LEFT(x, FIND(" ", x)+1)& " "&
                                RIGHT(x, LEN(x)-FIND("-", x)+1)))

Screenshots

Step 1

Step 2

Step 4

Step 5

Step 6 (Variation)

Step 7 (Variation)

1
  • 1
    Thanks so much for your efforts :)
    – HFE2022
    Commented Dec 14, 2022 at 13:20

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.