Help

Trying to use a formula to fix and then create a record name

Topic Labels: Formulas
Solved
Jump to Solution
369 2
cancel
Showing results for 
Search instead for 
Did you mean: 
CCS-tech
4 - Data Explorer
4 - Data Explorer

We have a table called Documents and I want to use a formula that pulls the name of the file in the {Attachment} field in order to provide the Name of the record. I've been using the LEFT({Attachments},FIND("(",{Attachments})-1) for this purpose and it works great, but I've now found that if the name of the attached file contains a comma, then the formula name adds a quotation mark at the beginning of the name. So now I'm trying to combine the SUBSTITUTE and FIND formulas to:
1. Look at the name of the attached file, and if it contains a comma then replace it with a hyphen (eg. 'Ocala, FL' becomes 'Ocala-FL' )  
2. Then use the name of the attached file as the name of the record without the URL at the end (using the FIND formula). 

Another acceptable option would be to just remove the comma from any file name in the {Attachments} field and then allow the original LEFT formula to do its work, but I think that might require an additional field. It's less elegant, but I'm okay with that. We have nearly 600 documents that have names with commas, and new ones are added regularly (we don't name the attachments ourselves), so not having to manually remove those commas would be fantastic.

2023-11-16_15-15-55.png

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

How about just removing the commas?

SUBSTITUTE(LEFT({Attachments},FIND("(",{Attachments})-1), '"', "")

 

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

How about just removing the commas?

SUBSTITUTE(LEFT({Attachments},FIND("(",{Attachments})-1), '"', "")

 

 Thank you for the quick help! I knew it had to be something fairly simple that I was missing. I tried so many ways to combine or nest those two formulas but could never get it quite right. Your solution works perfectly.