| Author |
Message |
jkaufman
Newbie
Joined: 20 May 2009
Location: United States
Online Status: Offline
Posts: 7
|
   Topic: Splitting Name field with non-standard data
Posted: 05 Sep 2017 at 6:50am |
|
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?
|
 IP Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 05 Sep 2017 at 8:13am |
|
Can you show some examples of the data to see if this is possible (use phony names).
|
|
IP Logged |
|
jkaufman
Newbie
Joined: 20 May 2009
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 05 Sep 2017 at 8:50am |
|
sure...
Data could be like any of the following:
Jennifer
Abby Atkins
Barbara Smith/Ricardo
Lisa Bush - Peggy Olsen
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 05 Sep 2017 at 9:59am |
|
And how would you want the data to look, especially the last two?
|
|
IP Logged |
|
jkaufman
Newbie
Joined: 20 May 2009
Location: United States
Online Status: Offline
Posts: 7
|
Posted: 05 Sep 2017 at 10:02am |
|
I would want the last two to look like
Barbara Smith
Lisa Bush
FirstName space LastName
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 06 Sep 2017 at 4:36am |
|
I believe the following formula should work in your situation.
Be sure to use Basic Syntax in the formula editor. Replace theame with your field.
dim i as number
for i = 1 to len(name)
if mid(name, i, 1) = "-" then
name = left(name, i-2)
exit for
else
if mid(name, i, 1) = "/" then
name = left(name, i-1)
exit for
end if
end if
next i
formula = name
|
|
IP Logged |
|
|
|
