INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Splitting Name field with non-standard data

Splitting Name field with non-standard data

(OP)
I have name data that needs to be split into first and last name.
However, the data in this field is not standard.
Some data just has the first name, some is standard with first name, space, last name.
Others have standard with first name, space, last name with either a "/" with no space after last name followed by other data or
a space and then further data which would not want to show this additional data.

Any suggestions on creating a formula for this?

RE: Splitting Name field with non-standard data

Hi,

Analyze your data and write a corresponding query to isolate certain data patterns that can then be parsed or corrected with a corresponding formula.

Lots of reiteration and work required. No way around it.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Splitting Name field with non-standard data

The following formulas may help you.

@FirstName
WhilePrintingRecords;
split({Sheet1_.Name}," ")[1];


@LastName
WhilePrintingRecords;
if (" " in {Sheet1_.Name}) then
split({Sheet1_.Name}," ")[2]else
if ("/" in {Sheet1_.Name}) then
split({Sheet1_.Name},"/")[2];


@LastnameWithoutAdditionalData
whileprintingrecords;
if "/" in {@LastName} then
split({@LastName},"/")[1]
else
{@LastName};


@Name
whileprintingrecords;
@FirstName}& " "& {@LastnameWithoutAdditionalData};

RE: Splitting Name field with non-standard data

The following formulas may help you.

@FirstName
WhilePrintingRecords;
split({Sheet1_.Name}," ")[1];


@LastName
WhilePrintingRecords;
if (" " in {Sheet1_.Name}) then
split({Sheet1_.Name}," ")[2]else
if ("/" in {Sheet1_.Name}) then
split({Sheet1_.Name},"/")[2];


@LastnameWithoutAdditionalData
whileprintingrecords;
if "/" in {@LastName} then
split({@LastName},"/")[1]
else
{@LastName};


@Name
whileprintingrecords;
{@FirstName}& " "& {@LastnameWithoutAdditionalData};

RE: Splitting Name field with non-standard data

Doing a Split on ALL your data may not work if your have first names that contain multiple words. Likewise with last names.

You first have to analyze your data and then categorize the data in such a way to group data with certain similarities that would enable you to use formulas like BettyJ suggested.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Splitting Name field with non-standard data

(OP)
Thanks to both of you for the help!
I shall test it out and see if Betty's formulas do the trick

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close