Split a string up from one column to 2 columns (First Name, Last Name)

[Josh1425]

Is it possible to have a full name go into a table and then split First name and Last name into their own columns?  Example: John Doe goes into one column but then John is put into another column and Doe goes into another.  

[Glitch]

Hi @Josh1425,

You can use this Formula to get the first name:

left([@field:Name], charindex(' ', [@field:Name])-1)

This on the last name:

right([@field:Name], (len([@field:Name])-charindex(' ', [@field:Name])))

 

Only problem I see here is if the User has Second name, the formula will just get the first part split by the space. And then the Last name will be considered all the string after the first name.

I hope this helps.

Glitch();

[PotatoMato]

Hi! Just to update this post, you can also refer here: https://howto.caspio.com/function-reference/

 

-Potato

[Wikiwi]

@Josh1425

You may want to also  check thins forum post. In your case you can change the ';' to a space.

 

[Tubby]

I think its always better to have it the other way around. Like let your users type their first name and last name separately. Reason being, it would be complicated to do this workflow if you have multiple word first name.

For example:

John Michael Doe

The result will be 

First name = John
Last name= Michael Doe

Which is incorrect. And yes, you can use CASE WHEN but then again, it will complicate things because you'll need to look for how many spaces are there.

So my suggestion is to do this the other way around, type the first and last name separately. Then, concatenate it using a formula/hidden calc value for the full name.

Just sharing my two cents 

[ParkLoey]

7 hours ago, Tubby said:

I think its always better to have it the other way around. Like let your users type their first name and last name separately. Reason being, it would be complicated to do this workflow if you have multiple word first name.

For example:

John Michael Doe

The result will be 

First name = John
Last name= Michael Doe

Which is incorrect. And yes, you can use CASE WHEN but then again, it will complicate things because you'll need to look for how many spaces are there.

So my suggestion is to do this the other way around, type the first and last name separately. Then, concatenate it using a formula/hidden calc value for the full name.

Just sharing my two cents 

definitely agree with @Tubby  

[@field:FirstName]+ ' ' +[@field:LastName]

Here's a sample of what we can use to concatenate fields so we form a whole name.

https://howto.caspio.com/function-reference/

[autonumber]

Hello! Just to update this post, to move the first and last name to their own column from the name field, you can use this formula as well. 

for first name: SUBSTRING([@field:name],0, CHARINDEX(' ',[@field:name]))

for last name; RIGHT([@field:name], LEN([@field:name]) - PATINDEX('% %', [@field:name]))

Hope this helps!  

[Kenzo]

Hi! Just to add to this post if the format is like this "Chris Dolls & Pat Doe" and you need to get only the first names of both full names, you can apply this formula.

"left([@field:FamilyName], charindex(' ', [@field:FamilyName])-1) +' & '+ left(right([@field:FamilyName], (len([@field:FamilyName])-charindex('&', [@field:FamilyName]))), charindex(' ', [@field:FamilyName])-1)"

Sample Result: