topic Re: vbscript to split a name field into two separate fields in ArcGIS Enterprise Portal Questions

vbscript to split a name field into two separate fields

TiaMorita — Tue, 04 Feb 2014 23:22:56 GMT

I have a table with a name field (where a name is like "John Smith") and I want to build a separate first_name and last_name fields. How can I populate each new field with just the first ("John) or last name ("Smith") using the original name field?

I'm fine with 2 sep scripts I plug into the field calculator for each new field, unless someone wants to suggest another way.

Thanks

Re: vbscript to split a name field into two separate fields

RichardFairhurst — Wed, 05 Feb 2014 04:37:05 GMT

I have a table with a name field (where a name is like "John Smith") and I want to build a separate first_name and last_name fields. How can I populate each new field with just the first ("John) or last name ("Smith") using the original name field?

I'm fine with 2 sep scripts I plug into the field calculator for each new field, unless someone wants to suggest another way.

Thanks

You have to calculate each field separately. Here are the two calculations you could use.

Split([Full_Name], " ")(0) ' Gets the first word

Split([Full_Name], " ")(Ubound(Split([Full_Name], " "))) ' Gets the Last Word

This really only works if the name is two words, but the code does not assume that. You could include an if then statement to deal with the situation where Ubound(Split([Full_Name], " ")) is anything other than 1 to decide if the additional words belong to the first name, the middle name, the last name or a company name (which you would not normally split). Technically if there is only one word in Full_Name then both functions will return that word (Ubound(Split([Full_Name], " ")) will equal 0).

Re: vbscript to split a name field into two separate fields

RobertBorchert — Wed, 05 Feb 2014 11:40:16 GMT

Never thought of using Unbound. But I like it.

A couple other methods. export a unique identifier along with the full name field to a .csv

in word open the .csv and replace all spaces with a tab. Save the .csv

open it in excel. update the column names. i.e. last and first names.

Now here you can error check for those names that may have a middle name or more than 2 words in the name.

Ideally you would end up with 3 columns. You can sort the 4th column and quickly see it there is anything there and fix it if there is.

open the .csv back in ArcGIS and join the unique identifiers up and update your feature with first and last.

Method 2. Create your first and last name fields. Assuming your field is called [FirstLast] and you want it split to [First] and [Last] I was going to give a fancy instring command but I would suggest using the unbound from the previous example to get the first part.

then for the second attribute mid([firstlast],((len([first])+1),100)

This will populate the [last] field with a string beginning at the character after the space and fill it in with the next 100 characters. If you [firstlast] field has entries longer than 100 characters then just make the number longer, won't hurt anything.

I have a table with a name field (where a name is like "John Smith") and I want to build a separate first_name and last_name fields. How can I populate each new field with just the first ("John) or last name ("Smith") using the original name field?

I'm fine with 2 sep scripts I plug into the field calculator for each new field, unless someone wants to suggest another way.

Thanks

Re: vbscript to split a name field into two separate fields

TiaMorita — Wed, 05 Feb 2014 15:27:46 GMT

Thank you both - both of your suggestions work great! 😄