# Creating a list of references from a text column

**URL:** <https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979>\
**Category:** Ask the Community\
**Created:** [April 7, 2023, 12:01pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979 "2023-04-07T12:01:40Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jack\_Beaudoin](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/jack_beaudoin/32/18630_2.png) [@Jack\_Beaudoin](https://connect.superhuman.com/u/Jack_Beaudoin)\
**Post date:** [April 7, 2023, 12:01pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/1 "2023-04-07T12:01:40Z")

</div>

Good morning,

I am wondering if anyone has a solution or suggestion to accomplish the following. In a table, I have a column of text in which I occasionally reference other rows such as @rowname. I would like to have a column that extracts all such references into a list. Ideally, I’d love to see Coda create a formula or column type that would do this automatically, but it is admittedly a niche use case…

So alternatively, I could imagine a formula that would take the text column and parse the text to create such a list. I love Coda’s formulas, but I’m not great with algorithms and code and I can’t quite figure out how to do this. Is there a way to identify such a reference as an object? Or would one have to treat each reference as a string of text?

The two conceptual difficulties for me are (1) defining the reference, because they can contain names of varying length and number of words; and (2) pulling multiple references from a text column (maybe use formulamap here?). I’d like to create an approach that could be applied to any available reference, whether it is a row, a table, a page, etc.

Finally — although I’d prefer not to — I would be willing to use a special character to “close” each reference, for example, using a “\<” as in _@rowname is quite long_ \< .

Thanks for any input or suggestions you might have.

Jack

---

<div class="post-metadata">

**Author:** ![Scott\_Collier-Weir](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/scott_collier-weir/32/29677_2.png) [@Scott\_Collier-Weir](https://connect.superhuman.com/u/Scott_Collier-Weir)\
**Post date:** [April 7, 2023, 12:25pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/2 "2023-04-07T12:25:40Z")

</div>

I think @Max_OBrien did this once, didn’t you?

---

<div class="post-metadata">

**Author:** ![Pch](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/pch/32/17688_2.png) [@Pch](https://connect.superhuman.com/u/Pch)\
**Post date:** [April 7, 2023, 1:14pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/3 "2023-04-07T13:14:39Z")

</div>

I think there’s a way using the _hidden/experimental_ `RegexExtract()` 😇  
… which seems to be the easiest way to do this.

```auto
References.Name.ForEach(
  RegexExtract(thisRow.Text,CurrentValue)
)

```

I used 2 tables in the sample below (one where the references are extracted and a second to store the references) so this might need some adjustments but all it does is it takes the text values in the whole field `Names` in my table `References` and `ForEach()` of these `Names` (stored as `CurrentValue`), I ask `RegexExtract()` to extract the value from the text in `thisRow.Text` 😊

https://coda.io/embed/E-kjmQdHMA/_suyZH?viewMode=embedplay&hideSections=true

This is just an idea 😇 …  
(And I really wish `RegexExtract()` would be made public at some point)

---

<div class="post-metadata">

**Author:** ![Scott\_Collier-Weir](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/scott_collier-weir/32/29677_2.png) [@Scott\_Collier-Weir](https://connect.superhuman.com/u/Scott_Collier-Weir)\
**Post date:** [April 7, 2023, 1:54pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/4 "2023-04-07T13:54:00Z")

</div>

Oh wow. That’s brilliant. And really cool

---

<div class="post-metadata">

**Author:** ![Pch](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/pch/32/17688_2.png) [@Pch](https://connect.superhuman.com/u/Pch)\
**Post date:** [April 7, 2023, 2:13pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/5 "2023-04-07T14:13:14Z")

</div>

Thank you @Scott_Collier-Weir 😊 !

---

<div class="post-metadata">

**Author:** ![Max\_OBrien](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/max_obrien/32/49285_2.png) [@Max\_OBrien](https://connect.superhuman.com/u/Max_OBrien)\
**Post date:** [April 7, 2023, 2:27pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/6 "2023-04-07T14:27:09Z")

</div>

RegexExtract() may be hidden, but it is reliable. I have hundreds of instances of it in a great many documents and it has not caused any problems.

BUT…

be aware that RegexExtract() as you use it here, will respond to ‘special’ characters in ways that you may not expect!  
`* ? [] { } ( ) ^ and .` all have specific meanings and there is a syntax that governs their use.

SO…

as long as your reference texts just contain alpha-numerics and spaces - you will be fine

but if it contains any of the characters mentioned above - then it will most likely fail to do what you expect - which is to find and extract the text.

ALTERNATIVE

Use the Split() formula to split-up your text where there is an @ symbol and then use Slice(2) to just take the 2nd and subsequent items and loop through the rows of the References table for each of them looking for a StartsWith() match. but that is a lot more complex.

Max

---

<div class="post-metadata">

**Author:** ![Pch](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/pch/32/17688_2.png) [@Pch](https://connect.superhuman.com/u/Pch)\
**Post date:** [April 7, 2023, 4:36pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/7 "2023-04-07T16:36:42Z")

</div>

I’m definitively not a RegEx expert @Max_OBrien 😇 ! (Still learning 😁 )

Thanks for your helpful input 🙌 !

I’ve tried the `Split()` by `@` method but I couldn’t make it work for some reasons 🤔 …

Would you mind, if you have a little bit of time to spare to share an example ?  
Maybe I just missed something (which is more than possible 😇 )

Another way, also involving a _hidden/experimental_ formula (`_Merge()`) would be to expose the “raw data” in the text field using something like :

```auto
thisRow.Text._Merge() + ""

```

And then, with the help of [ParseJSON()](https://coda.io/formulas#ParseJSON), retrieve the `name` of the reference within the text :

```auto
thisRow.[Text Raw data (JSON)].ParseJSON("$..name")

```

(I chose the name of the reference here but it could be something else)

I know it’s not a pretty way to do this though 😅 …

https://coda.io/embed/AxMp_jJXEL/_suyZH?viewMode=embedplay&hideSections=true

And I’m pretty sure @Paul_Danyliuk explained how this all work somewhere 😁 or @Christiaan_Huizer on his blog maybe ? 😊

---

<div class="post-metadata">

**Author:** ![Jack\_Beaudoin](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/jack_beaudoin/32/18630_2.png) [@Jack\_Beaudoin](https://connect.superhuman.com/u/Jack_Beaudoin)\
**Post date:** [April 7, 2023, 5:50pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/8 "2023-04-07T17:50:57Z")

</div>

Thanks so much. In your first suggestion, how did you create the “References (as ref)” column? I can recreate the “References (as text)” column…

Jack

---

<div class="post-metadata">

**Author:** ![Pch](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/pch/32/17688_2.png) [@Pch](https://connect.superhuman.com/u/Pch)\
**Post date:** [April 7, 2023, 6:15pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/9 "2023-04-07T18:15:20Z")

</div>

I just added a `Relation` field by clicking on the + at the end of the table and in the menu which pops up when the field is added, somewhere near the bottom, you should find the brand new `Relation` type of field (as since yesterday, [`Lookup` fields officially became `Relation` fields](https://connect.superhuman.com/t/launched-new-and-improved-ways-to-connect-data-with-linked-relations/38958)).

Then I simply selected the table `References`

 ![Add Relation](https://global.discourse-cdn.com/coda1/original/3X/2/b/2bac4dacf24fa5b58aff02508f513e5c8f3226e8.png)

And in `Relation options`:

 ![Relation options](https://global.discourse-cdn.com/coda1/original/3X/8/c/8c461a0921b7edf558b28ea2e171a46b90b2994d.png)

I clicked on `Add formula` :

 ![Relation Add Formula](https://global.discourse-cdn.com/coda1/original/3X/5/d/5dc9e7daf482bebd19e31363348dda0ee1f81686.png)

Which opened the formula editor where I’ve added the “all-in-one” formula just below :

```auto
References.Name.ForEach(
  RegexExtract(thisRow.Text,CurrentValue).WithName(Ref,
    References.Filter(Name.Contains(Ref) AND Name.IsNotBlank()).First()
  )
).Filter(CurrentValue.IsNotBlank())

```

It extracts each reference from the text (like in the field `References (as text)`) which is then stored within the value called `Ref` using [WithName()](https://coda.io/formulas#WithName) and then, using `Filter()` it retrieves the appropriate row in the table `References` by comparing the `CurrentValue.Name` to the value `Ref` (and this is done for each reference in `References.Name`)

But If you already have the “`References (as text)`” you can re-use those values in the `Relation` field too (if it’s easier for you) 😊 :  
In that case, the formula needed in the `Relation` field would look like this :

```auto
References.Filter(
  Name.Contains(thisRow.[References (as text)]) 
  AND Name.IsNotBlank()
  )

```

Hope this helps 😇

---

<div class="post-metadata">

**Author:** ![Jack\_Beaudoin](https://yyz1.discourse-cdn.com/coda1/user_avatar/connect.superhuman.com/jack_beaudoin/32/18630_2.png) [@Jack\_Beaudoin](https://connect.superhuman.com/u/Jack_Beaudoin)\
**Post date:** [April 7, 2023, 9:47pm UTC](https://connect.superhuman.com/t/creating-a-list-of-references-from-a-text-column/38979/10 "2023-04-07T21:47:11Z")

</div>

Helps a lot!

I simplified it by self-referencing the main table (in your example `Table`), and it is working like a charm. For anyone else who stumbles on to this thread, two things to note:

1. If you use a common word in the “Name” column, you will get unexpected references 🙂 whenever you use the word in the text column of other rows.
2. As Max pointed out, “_RegexExtract() as you use it here, will respond to ‘special’ characters in ways that you may not expect!_” I had a couple of references ending in question marks which nullified the regex match.