| Author |
Message |
crystalsonic
Groupie
Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
|
   Topic: MID function
Posted: 23 Jul 2013 at 5:18am |
I am using the MID function formula to extract the First Name from a Name column:
mid({Doctors.Name}, instr({Doctors.Name},',')+1)
This is what the formula gives me for a First Name:
Name
GATDULA, CRISTETA LORICA MD
First Name
CRISTETA LORICA MD
I need a couple of more things done:
1. I need to get rid of anything after the first name.
2. I need to get rid (trim) the leading space of the first name.
This what I want to see:
Name
GATDULA, CRISTETA LORICA MD
First Name
CRISTETA
|
 IP Logged |
|
|
praveeng
Senior Member
Joined: 11 Jul 2011
Online Status: Offline
Posts: 165
|
Posted: 23 Jul 2013 at 6:01am |
Hi,
Use the below code,
Create a formula(@formula1) with below code,
instr((Trim(mid({Doctors.Name}, instr(({Doctors.Name},',')+1)))," ")
Create another formula using below code
left((Trim(mid(({Doctors.Name}, instr(({Doctors.Name},',')+1))),{@Fromula1})
Place the Second formula in the designer
|
|
Praveen Guntuka,
praveen_guntuka@yahoo.com
|
|
IP Logged |
|
praveeng
Senior Member
Joined: 11 Jul 2011
Online Status: Offline
Posts: 165
|
Posted: 23 Jul 2013 at 6:04am |
Or
You can use it in single formula but it will some difficult to understand.
Left((Trim(mid(({Doctors.Name}, instr(({Doctors.Name},',')+1))),instr((Trim(mid({Doctors.Name}, instr(({Doctors.Name},',')+1)))," "))
--Praveen G
|
|
Praveen Guntuka,
praveen_guntuka@yahoo.com
|
|
IP Logged |
|
crystalsonic
Groupie
Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
|
Posted: 23 Jul 2013 at 6:40am |
I had to add a few ) in there and it worked fine. However, one problem, for the names that only have one first name, nothing is displaying:
Example:
Name
HURLOCK,DONNA
First Name
Name
HUBBARD ,ANGELA
First Name
This is the modified formula:
Left((Trim(mid(({Doctors.Name}), instr(({Doctors.Name}),',')+1))),instr((Trim(mid({Doctors.Name}, instr(({Doctors.Name}),',')+1)))," "))
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jul 2013 at 6:52am |
you could also try something like
split(trim(split({Doctors.Name},",")[2])," ")[1]
|
|
IP Logged |
|
crystalsonic
Groupie
Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
|
Posted: 23 Jul 2013 at 6:56am |
That worked great! Thanks for your help!!!
|
|
IP Logged |
|
crystalsonic
Groupie
Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
|
Posted: 23 Jul 2013 at 6:57am |
|
That worked great. Thank you!
|
|
IP Logged |
|
crystalsonic
Groupie
Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
|
Posted: 23 Jul 2013 at 9:23am |
I tried to export the report to EXCEL and I got the following error:
"A subscript must be between 1 and the size of the array"
It also highlights the formula. I am not sure which record generated the error though...
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 23 Jul 2013 at 9:32am |
you have records with no "," value?
if so what do you want to do wth them?
|
|
IP Logged |
|
crystalsonic
Groupie
Joined: 26 Jan 2012
Online Status: Offline
Posts: 46
|
Posted: 23 Jul 2013 at 9:48am |
Yes, I have a few that have no comma in the name. For those that do not have a comma, we can the whole name which would be Doctors. Name
This is how I have my formula set up:
If {Doctors.ClientInd} = 2 then
split(trim(split({Doctors.Name},",")[2])," ")[1]
else {Doctors.FirstName}
|
|
IP Logged |
|
|
|
