MID function
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19801
Printed Date: 02 May 2024 at 1:59pm
Topic: MID function
Posted By: crystalsonic
Subject: MID function
Date 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
|
Replies:
Posted By: praveeng
Date 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
|
Posted By: praveeng
Date 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
|
Posted By: crystalsonic
Date 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)))," "))
|
Posted By: DBlank
Date Posted: 23 Jul 2013 at 6:52am
|
you could also try something like
split(trim(split({Doctors.Name},",")[2])," ")[1]
|
Posted By: crystalsonic
Date Posted: 23 Jul 2013 at 6:56am
|
That worked great! Thanks for your help!!!
|
Posted By: crystalsonic
Date Posted: 23 Jul 2013 at 6:57am
|
That worked great. Thank you!
|
Posted By: crystalsonic
Date 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...
|
Posted By: DBlank
Date Posted: 23 Jul 2013 at 9:32am
|
you have records with no "," value?
if so what do you want to do wth them?
|
Posted By: crystalsonic
Date 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}
|
Posted By: DBlank
Date Posted: 23 Jul 2013 at 10:15am
|
did your formula work for you or do you still need some assistance on it?
|
Posted By: crystalsonic
Date Posted: 23 Jul 2013 at 10:45am
|
Yes, I still need assistance with this. I posted the formula how I currently working. I just need to add what to do when there is no comma in the Doctors.Name. Thank you!
|
Posted By: DBlank
Date Posted: 23 Jul 2013 at 10:49am
|
If {Doctors.ClientInd} = 2 then
split(trim(if instr({Doctors.Name},",")=0 then {Doctors.Name} else split({Doctors.Name},",")[2])," ")[1]
|
|