Skip to main content

Hello, I'm trying to figure out how I might be able to extract a portion of string relative to the position of a delimiter or other character.

For example, I have a table of Events that are structured as follows:

##### | <Name of Event>.

I want to be able to use the vertical bar "|" character as the item to extract each chunk of information:

##### and <Name of Event> respectively.

Any help with the formula logic here would be appreciated!

Hi Cody,

Here's some suggestions (assuming the field is called "Event"):  

- To return the first 4 characters of the field (this assumes there are always 4 characters to the left of the vertical bar: 
LEFT({event},4)
- Returning the characters after the vertical bar (again, assuming they always start at the 6th character and length is no more than 30 characters... if more, change the 30 to whatever number works):
MID({event},6,30)
- If the vertical bar is in various positions, you can use the FIND formula to find its position number:
FIND("|",{event})

You can nest the above functions as needed. For example, this will return the first 5 characters after the vertical bar:

MID({Events},(FIND("|",{Events})+1),5)

If you need additional help, feel free to schedule some time with me.  My Calendly link is: https://calendly.com/bruce_consulting/30min
 
Jody

Hi Cody,

Here's some suggestions (assuming the field is called "Event"):  

- To return the first 4 characters of the field (this assumes there are always 4 characters to the left of the vertical bar: 
LEFT({event},4)
- Returning the characters after the vertical bar (again, assuming they always start at the 6th character and length is no more than 30 characters... if more, change the 30 to whatever number works):
MID({event},6,30)
- If the vertical bar is in various positions, you can use the FIND formula to find its position number:
FIND("|",{event})

You can nest the above functions as needed. For example, this will return the first 5 characters after the vertical bar:

MID({Events},(FIND("|",{Events})+1),5)

If you need additional help, feel free to schedule some time with me.  My Calendly link is: https://calendly.com/bruce_consulting/30min
 
Jody

Thanks for your reply @bruceconsulting . In the meantime since I posted, I ended up creating a version of one of your suggestions, using the FIND() command since there is variability in the number of characters preceding and following the vertical bar. One piece of advice I could still use: I created two formula fields in order to get what I needed: First field is finding the location of the delimiter in the input string. The second formula I used the LEFT() command and the results of the FIND() command to isolate the desired characters. Am I missing another way to perform the whole operation in a single formula?

Delimiter Field:

 

 

 

FIND("|",Title)

 

 

 

"Trim" Field:

 

 

 

IF((FIND("|",Title))>0,LEFT(Title,Delimiter-1),"N/A")

 

 

 

 

Try this: 

IF(FIND("|",Title)>0,LEFT(Title,FIND("|",Title)-1),"N/A")

Jody

Much more elegant! Thanks so much Jody!

Reply


Terms & Conditions