Rollup + IF(SEARCH())

[DrShrink]

I tried to use rollup function to got previous data in the same table as below
First, I make the lenth of one field to be the same.
IF(LEN({TargetField})>10, LEFT({TargetField}, 10), {TargetField}&REPT("_", 10-LEN({TargetField})))
Then in a linked filed
I use Rollup functon ARRAYJOIN(values) in another table
Then look up the Rolluped field in the first table.
but When I tried to figure out the length of the lookup-ed rollup filed
It showed “error”

as example
Table one
(number field) (target filed) look-uped rollup filed
1 a 123 a-123 a-123,b-456,c-123,d-456
2 b 456 b-456 a-123,b-456,c-123,d-456
3 c 123 c-123 a-123,b-456,c-123,d-456
4 d 456 d-456 a-123,b-456,c-123,d-456

in second row
I tried to find the location of the word “b”
and the number between 2 to 4 characters before “b” is the data I want.
However , when I use Find or search function to the lookuped roll-up field
It’s always showed 0

Do anyone know what wrong it is, or how to mkae it in alterntive way…

[W_Vann_Hall]

Lookup fields often return arrays, even if there is only a single element in the array. I’d start by appending an empty string — &'' — to your lookup field and see if that clears the error message. Appending an empty string to a lookup field casts it as a string:

{LookupField}&''

[DrShrink]

Thanks, it works
Here is what I do to make this
let a record of weekly note show the note of last week as reference.

1. Add a string that is unique to the beginning of this week’s note
   For example first field of this recored - {Name}
   Formula =
   {Name}&{weeklyNote}

2. and make each note have the same length of words. like 100
   Formula =
   IF(Len({Step1})>100, LEFT({STEP1}, 100), {STEP1}&REPT("_", 100-LEN({STEP1})))

3. In another table linked to this table, Rollup Step 2 with
   CONCATENATE(Values)

4. In the original table, Lookup the Value of STEP 3.

5. Thanks to W_Vann_Hall, add a space to STEP 4
   {STEP4}&" "

6. Note of last Week
   RIGHT(LEFT({STEP5}, (FIND({NAME}, {STEP6}))-1), 100)

I think it can also be used to got previous data as number or others.

[W_Vann_Hall]

There’s another way to identify and display the most-recent [whatever], as @Matt_Bush explains in this reply Your method is awfully clever, though…