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

Let (
[
_words = "John Smith [email protected]" ;
];

List ( LeftWords ( _words ; 1 ) , MiddleWords ( _words ; 2 ; 1 ) , MiddleWords ( _words ; 3 ; 2 )  )

)

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).

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

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 )

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).

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]".

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