BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Select Part of String based on position of a text value


 
Search this topic... | Search Crystal Reports... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Author Message
ajax_83
Forum Member
Forum Member



Joined: 15 Oct 2010

Posts: 3



PostPosted: Fri Oct 15, 2010 12:08 am 
Post subject: Select Part of String based on position of a text value

Hi

I am trying to find a formula which will produce the following:

The string I have is in format "AAAA BRISBANE TO SYDNEY" I need to display in my crystal report everything from position 6 to " TO " ensuring that the spaces before and after TO are taken in to account as some of the words I would like to display in the report include TO in the name.

The resulting string to display in the report should simply read BRISBANE.

Any help will be much appreciated.

Cheers
Ajax_83
Back to top
idarif2007
Senior Member
Senior Member



Joined: 03 Jun 2010

Posts: 54
Location: India


flag
PostPosted: Sat Oct 16, 2010 4:34 am 
Post subject: Re: Select Part of String based on position of a text value

hi,


You need to use substring for this your formula would be something like this
=Substr([Variable];1;(Pos([Variable];"To")-1))

Regards
Arif
Back to top
ajax_83
Forum Member
Forum Member



Joined: 15 Oct 2010

Posts: 3



PostPosted: Sun Oct 17, 2010 8:11 pm 
Post subject: Re: Select Part of String based on position of a text value

Hi Arif

My crystal report is built on an SAP BEx Query and I do not seem to have the SubString formula available in the formula editor when I select either Crystal Syntax or Basic Syntax.

I have been trying to use a for loop this morning where I split the words out and then create a loop where it adds back one word at a time until the string "TO"occurs.

Once I have my local variable holding the correct string, I am unsure how to make sure it is passed back to the field value. Any suggestions?

Regards
ajax
Back to top
cr_manish
Principal Member
Principal Member



Joined: 30 Jul 2009

Posts: 126
Location: INDIA


flag
PostPosted: Tue Oct 19, 2010 2:31 am 
Post subject: Re: Select Part of String based on position of a text value

Hi Ajax,

Please try this formula:

mid('AAAA BRISBANE TO SYDNEY', 6,instr('AAAA BRISBANE TO SYDNEY',' TO ')-6)

Instr function gives the starting place of the sub string which is being serached within a string. Once the position of ' TO ' is known using Instr function, use Mid function to get required string from the 6th postion.

Cheers!!!
Manish
Back to top
ajax_83
Forum Member
Forum Member



Joined: 15 Oct 2010

Posts: 3



PostPosted: Tue Oct 19, 2010 6:17 pm 
Post subject: Re: Select Part of String based on position of a text value

Hi Manish

Thanks for your post. I tried this but get a message value is less than 0 or not an Integer. I have solved this last night by switching the formula view to Basic Syntax and using VB Script as shown below. Looks a little messy but does the job.

If {string} startswith "AAAA" Then
Dim i as Number
Dim strVal as String
Dim strArray() as String
strArray = split({string})

For i = 1 To UBound(strArray) Step 1
If strArray(i) = "TO" Then
Exit For
End If

strVal = strVal & " " & strArray(i)
Next
Formula = Replace(strVal, "AAAA ", "")
Else
Formula = string
End If
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0461 seconds using 17 queries. (SQL 0.0020 Parse 0.0354 Other 0.0087)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo