Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Split Data


Garry Bargsley

Recommended Posts

Hello all...

 

I have a field called name that looks like the following:

 

CORREA, ROMANITA CRYSTAL

 

 

What I need to do is split this record in to three parts.

 

Last Name part = All data up to the ","

 

First Name Part = from "," to first "space"

 

Middle Name Part = Anything after the first "space" will fall in to middle name

 

I have 4 fields already defined:

 

Name

LastNAme

FirstName

MiddleNAme

 

Any help on this would be great.

Link to comment
Share on other sites

Use the LeftWords/MiddleWords/RightWords functions like this:

 

FirstName=MiddleWords(Name; 2; 1) - the 2 is to tell the function to start looking at the second word and the 1 is to tell the function that you want only one word.

 

MiddleName=RightWords(Name; 1) - this gets 1 (as specified) word starting from the right.

 

LastName=LeftWords(Name; 1) - same as above but starting from the left.

 

Beware, though - if you have entries in the Name field with anything more than three names, like the one in your example, you'd be better off just using MiddleWords.

Link to comment
Share on other sites

This formula also gives unexpected values with two names as then FirstName and MiddleName will be the same. Replace the MiddleName Formula by

MiddleName=MiddleWords(Name;3;WordCount(Name) - 2)

Link to comment
Share on other sites

if you have a comma at your disposal, why not use it?

 

lastname=Trim(Left(name; Position(name;",";1;1)-1))

 

firstname=Let (Birthnames=Trim(Right(name; Length(name)-Position(name;",";1;1))); Middle ( Birthnames ; 1 ; Position(Birthnames;" ";1;1)))

 

middlenames=Let (Birthnames=Trim(Right(name; Length(name)-Position(name;",";1;1))); Trim(Substitute(birthnames;firstname;"")))

 

kjoe

Link to comment
Share on other sites

I would use

 

FirstName: Trim(Substitute( MiddleValues( Substitute( Name; [" "; ¶] ); 2; 1 ); ¶; "" ))

 

MiddleName: TrimAll( Right( Name; Length(Name) - Position( Name; " "; 0; 2 ) ); 0; 0 )

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use