If only four possibilities that will not change.
A Case Statement would be easy.
If the first letter is U, set the field to "UT Dallas"
Here are the steps I go through in order to figure out what my calculation will look like:
- Review multiple samples of the data to be parsed. If you only use one example, you’re bound to miss the nuances of “more complex” examples.
- Look for patterns. Ask yourself, “If I had to do this manually, what would I be looking for?” Look for things like:
- X always starts with Y
- There is always an X before/after Y (where X could be a comma, semicolon, colon, etc.)
- X will always be Y number of characters
- Don’t focus too much on extraneous stuff. What I mean is there are great tools in FileMaker to filter (hint, it’s the “Filter” function!) text down to the “wanted” characters.
- Love and respect the “Let” function. The beauty of the let function is that it lets you take parsing one step at a time, with minimal confusion. See the examples below for proof. Also, use “white space” or spaces/returns within your calculation so that it’s easy to re-read your calculation later, along with comments to explain what’s going on.
- Test, test, test. In most cases, the Data Viewer is the best way to parse out text so you can see your calculation working against live data.
Here is a list of common functions when parsing data:
Text functionsText MiddleWords function to extract specific words from supplied text.can be used to analyze, rearrange, extract, and build text strings. For example, you could use theText functions operate on these parameters:•fields of type text•text(in quotation marks)•having a text resultClick a function name for details.
- Left or LeftWords
- Right or RightWords
- Middle or MiddleWords
This functionReturnsCharReturns the characters for the Unicode code points in the number.CodeReturns the Unicode code points for the characters in the text. If zero characters are in the text, returns 0.Exact1 (True) for an exact match, or 0 (False) for a mismatch between two text strings or .FilterOnly the specified characters, in the order that they were originally entered in the text.FilterValuesOnly the specified values, in the order that they were originally entered in the text.GetAsCSSThe specified text, converted to theformat.GetAsDateDates in the specified text asdate, for use in involving dates or date functions.GetAsNumberNumbers in the specified text as field type number, for use with formulas involving numbers or numeric functions.GetAsSVGThe specified text, converted to the SVG (Scalable Vector Graphics) format.GetAsTextThe specified number, date, time or timestamp as field type text, for use with formulas involving text or text functions.GetAsTimeTimes or timestamps in the specified text as field type time, for use with formulas involving the time or timestamp functions.GetAsTimestampThe specified data as field type timestamp, for use with formulas involving timestamps.GetAsURLEncodedThe specified text, converted with URL (Uniform Resource Locators) encoding.GetValueA specific value from a list of values.HiraganaHiragana converted from Katakana (Hankaku and Zenkaku).KanaHankakuHankaku Katakana converted from Zenkaku Katakana.KanaZenkakuZenkaku Katakana converted from Hankaku Katakana.KanjiNumeralKanji numerals converted from Arabic numerals.KatakanaZenkaku Katakana converted from Hiragana.LeftThe specified number of characters in the text, counting from the left.LeftValuesThe specified number of values in the text, counting from the left.LeftWordsThe specified number of words in the text, counting from the left.LengthThe number of characters in the specified text, including all spaces, numbers, and special characters.LowerAll letters in the specified text as lowercase.MiddleThe specified number of characters in the text, starting at a specified character position.MiddleValuesThe specified number of values in the text, starting with a specified value.MiddleWordsThe specified number of words in the text, starting with a specified word.NumToJTextRoman numbers converted from Japanese text.PatternCountThe number of occurrences of a search string in the specified text.PositionThe specified occurrence of a search string, starting from a specified position.ProperThe first letter of each word in the specified text as uppercase, and all other letters as lowercase.QuoteThe specified text surrounded by quotation marks (“ ”).ReplaceA new string of characters consisting of the specified text as modified by the specified replacement text.RightThe specified number of characters in the text, counting from the right.RightValuesThe specified number of values in the text, counting from the right.RightWordsThe specified number of words in the text, counting from the right.RomanHankakuHankaku (alphanumeric & symbols) converted from Zenkaku (alphanumeric & symbols).RomanZenkakuZenkaku (alphanumeric & symbols) converted from Hankaku (alphanumeric & symbols).SerialIncrementThe combined text and numbers in a specified value, with the numbers incremented by the specified amount.SubstituteA text string with every occurrence of a specified search string in the text replaced by a specified replacement string.TrimText stripped of all leading and trailing spaces.TrimAllText with full width spaces between non-Roman and Roman characters removed.UpperAll letters in the specified text as uppercase.ValueCountA count of the total number of values in the specified text.WordCountA count of the total number of words in the specified text.
Try the Position function to find the # then extract the "beginning" string.Example "UT Dallas #01" is the Textfield, then the Position of "#" is 11then use the Left function to get the first 10 characters such as:Left(Textfield, Position(Textfield;"#";1;1) -1) will return "UT Dallas " Note blank after Dallas. If you don't like the trailing blank use Trim function.Then use Right funtion to get the "#01"Right(Textfield, Length(Textfield)-Position(Textfield;"#";1;1) ) will return "#01"
Just a thought. If the number of characters from the # to the end are always the same, Use an if/then statement and the Right function to delete the last X number of characters and paste the results into your previously created New Field.
If this text fields/values are static and its last characters including "#" are same then go for Frank's solution.
If this values are dynamic then go for Jim's answer.Actually dynamic solutions are the best and we generally go for this for better performance.