I have the following input and formula:
In table column "inputLiveOps[Agent Full Name]" I have the following text values:
"doej (John)" <------------- ideal scenario
"smithd (Dennis)" <------- extra space between first initial and opening bracket
"knoxj (Jim )" <------------ extra space between end of first name and closing bracket
Desired results: Name in format "Last, First".
Here is my formula:
'Checks for empty
=IF(inputLiveOps[@[Agent Full Name]]="","",
'Gets last name
PROPER(LEFT(inputLiveOps[@[Agent Full Name]],
'Checks for extra spaces between first initial and opening bracket
IF(RIGHT(LEFT(inputLiveOps[@[Agent Full Name]],SEARCH("(",inputLiveOps[@[Agent Full Name]])),3)=" (",
'Value if true ie extra space
SEARCH("(",inputLiveOps[@[Agent Full Name]])-4,
'Value if false ie no extra space
SEARCH("(",inputLiveOps[@[Agent Full Name]])-3)))
'add ", " to format name as "Last, First"
&", "
'Get first name from between the () brackets
&MID(inputLiveOps[@[Agent Full Name]],SEARCH("(",inputLiveOps[@[Agent Full Name]])+1,
'if extra space after first name
IF(RIGHT(inputLiveOps[@[Agent Full Name]],2)=" )",
'value is extra space
SEARCH(")",inputLiveOps[@[Agent Full Name]])-SEARCH("(",inputLiveOps[@[Agent Full Name]])-2,
'value if no extra space
SEARCH(")",inputLiveOps[@[Agent Full Name]])-SEARCH("(",inputLiveOps[@[Agent Full Name]])-1)))
Is there a better way to deal with extra spaces? It seems very clumsy to have two IF statements in the middle of the formula.
Full formula for pasting:
![]()
Please Login or Register to view this content.
Bookmarks