I’m trying to copy text from a subtitle file into the latest version of libreoffice calc and find a way to have it read the text and look to see if it contains Dialogue: 0,0:00:00.00,0:00:00.00,Default,0,0,0, and if it does, to cut only that portion of the text into another cell on that row named ‘timing information’. I ask this to save having to copy and paste the entire line then cutting it manually and pasting it again since I’ll be doing this thousands of times. I looked into if then statements but didn’t find one for something this specific. Thank you in advance.
You can use SEARCH formula to find if desired text string is presented in the cell. Assuming source text is in A1 cell:
IFERROR(SEARCH("Dialogue: 0,0:00:00.00,0:00:00.00,Default,,0,0,0,,";A1;1);0)
This will return 0 if text string is not found and 1 if text string is found in A1 cell.
Now you can fill the helper column with this formula and use autofilter to filter only rows with value 1. This will show you only rows where desired text is present, so you can fill “timing information” column with your text.
You can also nest above expression in IF statement and use it directly in “timing information”:
IF(IFERROR(SEARCH("Dialogue: 0,0:00:00.00,0:00:00.00,Default,,0,0,0,,";A1;1);0)=0;"";"Dialogue: 0,0:00:00.00,0:00:00.00,Default,,0,0,0,,")
This will return “empty” value if text is not present and the text string itself if text is found.
Regarding source text where you need to “cut” the text. You can preserve the source text and use helper column with SUBSTITUTE to get the desired result:
SUBSTITUTE(A1,"Dialogue: 0,0:00:00.00,0:00:00.00,Default,,0,0,0,,","")
Or you can select cells with the source text, open Find&Replace dialog, tick “Current selection only” in Other options and replace “Dialogue: 0,0:00:00.00,0:00:00.00,Default,0,0,0,” with nothing. NB! This will affect SEARCH and IF formulas above, so make sure the desired calculations are copied.
Forgot to reply. I figured it out. Formulas don’t seem like it can do that task. I instead created a macro to get the job done. The 0s next to dialogue are almost never going to be 0s since it’s subtitle info so the find and replace suggestion doesn’t work. The MID function works for taking out what I want but there’s no function for deleting anything from a cell and carrying it to another cell. Thanks for getting me started. What I am doing in my macro is first searching if a cell includes the timing information, then taking out the hours, minutes, seconds portion and converting that to just seconds and pasting that into another column then it deletes the timing information out the cell it got the info from. I’m converting it to seconds since I’m going to put all the timing info into a txt document and import it to audacity. Here’s my code. Please excuse my messy programming. First time writing a macro.