Trim a text string based on the occurrence of a character NOT on # of characters

[chikanyc]

Is there a calculation that will take the text from one field and based on the occurrence of a certain character, delete everything before it?

E.g. I have a field called Song Title. In the song title, we might have values like "Christmas Song - w/Horns", "w/Organ" or "Christmas Song".

I want to create a new field that will describe what song version is for the song/record. So the new Version field (calc) would show a result of "w/Horns", "w/Organ" or "".

Does anyone out there know a way to do this? Thank you:)

[fseipel]

If the song title is in a field called Song_Title, and the substring ("Christmas Song") to clip is in a field called substring, use substitute:

New field without substring = Substitute ( text ; searchString ; replaceString )

Specifically,

New field without substring = Substitute (Song_Title;substring & " - ";"")

New field without substring = Substitute (New field without substring;substring ;"")

This replaces all occurrences of substring with nothing (""); the reason to do it twice is to handle the instance of Christmas song with a space, hyphen, space; doing it a second time covers the situation in which there is no hyphen.

If there is text before the substring which also needs deleted, I'd suggest using the Position, Middle, and Length text functions. e.g. for a hyphen, to delete everything before it, if it is in string:

Case ( Position ( Generic_Test::String_to_Search; "-";1 ;1)>0; Right ( Generic_Test::String_to_Search;Length ( Generic_Test::String_to_Search ) - Position ( Generic_Test::String_to_Search; "-";1 ;1)-1); Generic_Test::String_to_Search)

(this returns Chrismas Song if there is no hyphen; if instead you want it to return "", change last Generic_Test::String_to_Search to read ""; above returns all the characters right of the search string. I subtract 1 to also clip the space trailing the hyphen.

[chikanyc]

Hi fseipel,

Thanks for replying:) I'm trying to follow what you're saying but having some trouble.

Every song title is unique. "Christmas Song - w/Horns"; "Wind Song - w/ Strings", "Wind Song" etc.

I want to have two calculated fields broken out from my current Song Title field.

If the Song Title is "Christmas Song - w/Horns"

Song Base field = "Christmas Song"

Version field = "w/Horns"

or if the Song Title is "Wind Song"

Song Base field - "Wind Song"

Version field = ""

Because the characters of each song title varies, it's hard to type in a substring or use Position, Middle and Length text functions...does that make sense?

Thanks for reading:)

[Lee Smith]

These calculations are from [color:orange]comment that he posted a while back. They should do what you asked.

For the the [color:blue]Song Base field, and new calculation field, result of text;

Left ( Song Title ; Position ( Song Title ; "-" ; 1 ; 1 ) - 1 )

For [color:blue]Version field, new calculation field, result of text

Right ( Song Title ; Length ( Song Title ) - Position ( Song Title ; "-" ; 1 ; 1 ) )

Lee

[chikanyc]

That worked perfectly! Thanks so much!!!