Use multiple text strings to extract info and populate fields

Coordinators_AU · ‎Mar 27, 2019

Hello, everyone!

I have an issue I thought was simple, but is anything but, that I was wondering if the community is able to assist me with.

I’m integrating an airtable with Deputy to display information specifically relevant to me. So Deputy generates ‘notes’ which I can bring into an airtable, but I would like to grab specific information from the notes and use this specific information to populate different fields of my airtable.

For example :

I am importing this from Deputy -

Title: RXXXXXX Webcast 27 March;
Live: 16:00 - 18:00;
ID: AUXXXB2703A;
Client: RXXXXX Conferencing;
User: ChXXX GoXXz;
Transcript: yes, 120 mins;

So, I am trying to search for the particular strings, such as ‘Title’, ‘ID’, ‘User’, etc etc and then use the information that comes after these tags and before the ‘;’ to populate different fields of an airtable.

I’ve had varying success using different methods, but only been able to return the first tag, being ‘Title’.

My process has been to import the information from Deputy into the primary field, then attempt to use that information to populate the different fields I need.

From my understanding, I feel like I should be able to search for the particular string, then pull everything from that until the next semicolon, but I am unable to make it work.

Any help would be hugely appreciated.

Thank you!

JonathanBowen · ‎Mar 27, 2019

Hi @Coordinators_AU - have a look at this example:

I have used 3 fields to extract the ID value and you could use the same method for the other elements of the string. The process is pretty much as you say above:

Find the position of the ID string
Find the position of the next semicolon
Use these two values to pull out the ID value

As you can see in the formulas, the MID function on the string needs to be offset by 4 characters to reflect the length of the string "ID: " (ID, colon, space). Similarly, “Client” would be offset by 8 chars "Client: ".

The methods means that the ID or any other element can be off different lengths and will still work as you are relying on the position of the key and the semicolon to determine the extract.

Hope this helps!

JB

Kamille_Parks · ‎Mar 27, 2019

You could also use the following formula. Make one field per item you want pulled out of the text (1 for Title, 1 for Live, etc)

MID({Deputy Import},FIND('Title: ',{Deputy Import})+7,FIND('\n',SUBSTITUTE({Deputy Import},LEFT({Deputy Import}, FIND('Title: ',{Deputy Import})),''))-8)

In this field, its looking to extract the title information. Title information is preceded by a 7 character string ('Title: '). The ‘8’ in the formula is to account for those 7 characters plus the semicolon at the end of the line.

Use this same structure for the remaining fields, adjusting FIND() portions accordingly and using the correct string length (7). For 'Live: ’ 7 would change to 6, 'ID: ’ would be 4, etc.

David_Skinner · ‎Mar 27, 2019

Making columns that calculate the colon and semi colon positions is probably easiest. Call them 1st colon, 1st semi-colon, 2nd colon, 2nd semi-colon etc.

The formulae in these fields should be:

1st colon = FIND(":", Deputy)
1st semi-colon = FIND(";", Deputy)

2nd colon = FIND(":", Deputy,{1st semi-colon})
2nd semi-colon = FIND(";", Deputy,{2nd colon})

3rd colon = FIND(":", Deputy,{2nd semi-colon})
3rd semi-colon = FIND(";", Deputy,{3rd colon})

You can now use these fields to find the individual headings:
Title = TRIM(MID(Deputy,
{1st colon}+1,
{1st semi-colon}-{1st colon}-1))

Live = TRIM(MID(Deputy,
{2nd colon}+1,
{2nd semi-colon}-{2nd colon}-1))

ID = TRIM(MID(Deputy,
{3rd colon}+1,
{3rd semi-colon}-{3rd colon}-1))

etc.

Coordinators_AU · ‎Apr 03, 2019

Thanks for this, guys!
Between these different answers, I was able to get this working perfectly!

I do have a follow up question, though -

What is the best way to organise fields from the same row into a separate column?

For example, I used the previous strings to create multiple instances of the same trigger, so I can create fields that will be populated with anything in between “Live:” and a semi-colon, no matter how many times it appears, as long as there is a separate field for them.

My question is, can I then use those multiple instances that are on the same row and put them into different fields on the same column?

This is the result of running the search strings over my text dump - three different fields.
‘Seven Early News’, ‘Sunrise’ and ‘The Morning Show’.
If I was then able to them into the one column, is there a way to do that? Even if I have to use a third-party?

Thank you.

Kamille_Parks · ‎Apr 04, 2019

could you just make a new formula field like {Title 1}&','&{Title 2}&','&{Title 3}

Coordinators_AU · ‎Apr 04, 2019

This combines them all into the same record, unfortunately I need them to generate a new record for each.

Kamille_Parks · ‎Apr 04, 2019

Add a Link to Another Record field pointing to a [Title] table, then copy and paste the concatenated formula field with all three titles into the Link.

Also, you’ll want to clean up your {Title 2} and {Title 3} formula fields by by removing the colons. SUBSTITUTE({your current formula},': ', '')

Deborah_Bensted · ‎Jun 06, 2019

Hi there … thank you for this … I have just used it to break up a complex field and it is much appreciated. Deb

ben_pappas · ‎Dec 21, 2019

Hi Jonathan

Are you able to paste the formulas here in the Base you displayed?