Philip Jenks Posted June 5, 2014 Share Posted June 5, 2014 Hi I've imported names and addresses from a spreadsheet in this format (all in a single FM field) John Smith [email protected] Can anyone suggest the calculations that will separate this into: John Smith [email protected] Thanks! Philip Link to comment Share on other sites More sharing options...
Kris M Posted June 5, 2014 Share Posted June 5, 2014 Let ( [ _words = "John Smith [email protected]" ; ]; List ( LeftWords ( _words ; 1 ) , MiddleWords ( _words ; 2 ; 1 ) , MiddleWords ( _words ; 3 ; 2 ) ) ) Link to comment Share on other sites More sharing options...
comment Posted June 5, 2014 Share Posted June 5, 2014 MiddleWords ( _words ; 3 ; 2 ) This is not a good idea, because a valid e-mail address can contain other word-delimiting characters besides @, such as a hyphen or an underscore. Another example where this would fail is "[email protected]". Try instead: Substitute ( YourField ; " " ; ¶ ) Use GetValue() to extract the individual components: FirstName = GetValue ( Substitute ( YourField ; " " ; ¶ ) ; 1 ) LastName = GetValue ( Substitute ( YourField ; " " ; ¶ ) ; 2 ) e_Mail = GetValue ( Substitute ( YourField ; " " ; ¶ ) ; 3 ) Note that we are assuming that the field always contains exactly two spaces (so no middle names, for example). 1 Link to comment Share on other sites More sharing options...
Kris M Posted June 5, 2014 Share Posted June 5, 2014 comment.. your right about that but the OP didn't request a solution for every possible scenario. If he did i probably would not have replied because the calc would be extreme Link to comment Share on other sites More sharing options...
Lee Smith Posted June 5, 2014 Share Posted June 5, 2014 Hi comment, You have a typo on your calculation. The YourField in the different calculations (except the first one) needs to be changed to Try instead field. i.e. GetValue ( Substitute ( YourField ; " " ; ¶ ) ; 1 ) s/b GetValue ( Substitute ( Try instead ; " " ; ¶ ) ; 1 ) Link to comment Share on other sites More sharing options...
comment Posted June 5, 2014 Share Posted June 5, 2014 You have a typo on your calculation. The YourField in the different calculations (except the first one) needs to be changed to Try instead field. LOL. No, that's not quite what I meant (see edit). Link to comment Share on other sites More sharing options...
comment Posted June 5, 2014 Share Posted June 5, 2014 comment.. your right about that but the OP didn't request a solution for every possible scenario. If he did i probably would not have replied because the calc would be extreme I don't think having a different name and e-mail in each record counts as "every possible scenario" or requires an "extreme" calculation. It's just that expecting every e-mail to count as 2 words is not reasonable. Going with your method of counting words, the last one would need to be = RightWords ( Yourfield ; WordCount ( Yourfield ) - 2 ) assuming (again!) that the names count as one word each. This may be a more reasonable expectation, but it would break on "Anne-Nicole Smith [email protected]". Link to comment Share on other sites More sharing options...
Lee Smith Posted June 5, 2014 Share Posted June 5, 2014 LOL. No, that's not quite what I meant (see edit). I was looking at the YourField as his text import field, not his current calculation field. DOH Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3606 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now