Your text field shows 2 commas--one just before the first name and one just after the middle initial. Does every single record adhere to that format? In this example, do you want to eliminate all text except "John T." or do you just want "John"?
You'll need to be careful with any such process as there can be many individual differences in how names are recorded and it's easy to use a solution that works most of the time, but fails with one or two special cases.
Note: the pattern here suggests that the data was exported as a csv (comma separated value) file. Did you select the CSV file type when importing the data. That option might eliminate the need for much of the special processing to separate out the parts of a person's name.
There is only one comma that is before the First name currently. My example of John T. and Sarah Sally and Mr. & Mrs. do not have commas. I was just typing the comma between these as a proper English seperator in my post. Sorry.. If I could just keep everything to the right of the comma I would be happy.
Let ( [ t = YourTextfield ;
L = Length ( t ) ;
p = position ( t ; "," )
Right ( t ; L - p )
This looks like a solution that would get me out of a sticky situation.
I have pasted the calculation but keep getting an error that there are too few parameters in this function.
YourTextField has been replaced by the one from my field list
The last close bracket on line three becomes highlighted.
Regards and ATB
As you know there are formatting for number fields, however I too have face an issue here I needed the field to be a text field, but if a number is entered, to follow some formatting. I did a Custom Function that will add a "0" or ".00" if needed and put a comma at the thousand position. If you need more then that you will have to change it a bit.. I'm sure that this is a bit crude for some of you advanced users, so feel free to upgrade it....
Hope this will help!!!
If (Filter ( Field1 ; 0123456789 );
If (Middle ( Field1; Length ( Field1 )-2 ; 1 )= "." ; If ( Length ( Field1 )>6 ; Left ( Field1 ; (Length ( Field1 ))-6 ) & "," & Right ( Field1 ; 6 ) ; Field1 ) ;
If ( Middle ( Field1 ; Length ( Field1 )-1 ; 1 )="." ;
If ( Length ( Field1 & "0" ) >6; Left ( Field1 & "0" ; (Length ( Field1 & "0" ))-6 ) & "," & Right ( Field1& "0" ; 6 ) ; Field1 & "0" ) ;
If ( Length ( Field1 & ".00" ) >6; Left ( Field1 & ".00" ; (Length ( Field1 & ".00" ))-6 ) & "," & Right ( Field1& ".00" ; 6 ) ; Field1 & ".00" ) ) ) ;
Proper ( Field1 ))