I'm trying to concatenate values based on the value of a cell, however, this requires a logical function. I've tried it with both FIND, MATCH and SEARCH, but it's not outputting the expected results.
How do I get it to work expectedly?
Suppose the expected result must be an email address generation where it is in the form of firstname@company.com, but you may have values which looks like "John Doe", "Sales", "John", etc.
This is the functions which I have used, without any success:
MATCH
Function
=IF(COUNTBLANK([@Name]) = 0; IF(MATCH(" "; [@Name]; -1) = 1; CONCATENATE([@Name]; "@company.com"); CONCATENATE(LEFT( [@Name]; FIND( " "; [@Name] ) - 1 ); "@company.com")); "")
Note: Match parameters have been tested amongst -1, 0 and 1. -1 provides the best results.
Result
Where the lookup cell contains "John Doe", the concatenation result looks like John [email protected], and "John" looks like [email protected]. This does not seem to work. When parameters for MATCH changes to 0 or 1, the result is #N/A on all.
FIND
Function
=IF(COUNTBLANK([@Name]) = 0; IF(FIND(" "; [@Name]) = 1; CONCATENATE([@Name]; "@company.com"); CONCATENATE(LEFT( [@Name]; FIND( " "; [@Name] ) - 1 ); "@company.com")); "")
Result
Where the lookup cell contains "John Doe", the concatenation result looks like [email protected], and "John" looks like "#VALUE!". This does not seem to work effectively.
SEARCH
Function
=IF(COUNTBLANK([@Name]) = 0; IF(SEARCH(" "; [@Name]) <= 1; CONCATENATE([@Name]; "@company.com"); CONCATENATE(LEFT( [@Name]; FIND( " "; [@Name] ) - 1 ); "@company.com")); "")
Result
Where the lookup cell contains "John Doe", the concatenation result looks like [email protected], and "John" looks like "#VALUE!". This does not seem to work effectively.
