Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Extract parts of a text field

Posted on 2013-05-13
25
Medium Priority
?
273 Views
Last Modified: 2013-05-17
Hi Experts

I have to come up with a way to extract parts of text in a field.  Challenge is that it doesn't always follow the same format.  I think there are enough similarities that it can be done but it eludes me as to how to figure it out.  

Here is an example of the text:

BWPA00152wMontoSun5.99/wk
BWPA01252wSatSun1.96/wk
BWPA01212wkSatSun1.7/wk
BWPA01452wkMontoFri3.25/wk
BWPA01312wkMontoSat3.83/wk
BWPP1498wMonToSun2.8/wk
BWPP16552wkMontoSun1.6/wk
BWPP17112wkMontoFri3.34/wk
BWPP17252wkMonToFri0.8/wk
BWPP17724wMontoSun1.9912wFree


What I need to extract is the amount of weeks directly in front of the "days of the week".  Some end in a "w" and some in "wk".  I don't want the alpha "w" or "wk" but the 2 digits in front of it.  The second part is to extract the dollar value in front of the last "w" for instance 1.99.  

Any help would be greatly appreciated.  

A
0
Comment
Question by:spudmcc
  • 13
  • 9
  • 3
25 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 39162737
THe first one is relatively easy.

Right(Split({YourField},"w")[1],2)

The second is complicated by the last line and by the fact the numbers are various length.

Is the last entry accurate?

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39162750
I was able to find another table in our DB that might make the 2nd part easier.

The sample is:

BWP Sun $1.07
BWP Sun $1.27
BWP SatSun $2.14 per wk
BWP SatSun $2.54
BWP FriToSun $3.03 per wk
BWP FriToSun $3.59 per wk
BWP MonToFri $4.46 per wk
BWP MonToFri $5.39 per wk
BWP MonToSat $5.53 per wk
BWP MonToSat $6.75 per wk

Where I would need the dollar value behind the "$"
0
 

Author Comment

by:spudmcc
ID: 39162765
For the first part I need the text in front of the "Mon-Sat" or whatever the days of the week are.

BWPA001           52             wMontoSun5.99/wk       I need the 52.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 101

Expert Comment

by:mlmcc
ID: 39162771
The formula above should give you the 52 assuming it is always 2 digit.

Try this ofr the other

SPlit(Split({YourField},"$")[2])," ")[1]

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165236
It keeps giving me the message "the ) is missing".  I can't see any missing ).  I've tried several things but it is eluding me how to correct this.  When I add a ) it tells me that the rest of the statement doesn't seem to belong.  

A
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39165287
Which formula?

Can you copy it here?

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165291
SPlit(Split({RateCode1.Description},"$")[2]),"")[1]
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39165302
Try

SPlit(Split({RateCode1.Description},"$")[2]," ")[1]

There was a ) after [2]

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165312
Now it gives me the message "A subscript must be between 1 and the size of the array".  I am sorry but I am not familiar with this message.  

A
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39165318
Can the field be NULL or not have the $?

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165339
Good question...when I went back and looked at the raw data some don't have a $.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39165389
IS there anything that can be used to determine the start of the numeric field?

Can you post a couple of samples without the $

This will get the values from the ones with $

If InStr({RateCode1.Description},"$") > 0 then
    SPlit(Split({RateCode1.Description},"$")[2]," ")[1]
Else
     "0"

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165553
Bos, White Oak, Philly SatSun
Bos, White Oak, Philly FriToSu
Boston\Whiteoka\Philly Sat&Sun
BWPA00012wkMontoSun5.31/wk
0
 

Author Comment

by:spudmcc
ID: 39165560
If InStr({RateCode1.Description},"$") > 0 then
    SPlit(Split({RateCode1.Description},"$")[2]," ")[1]
Else
     "0"

This still returned the subscript error.  "A subscript must be between 1 and the size of the array"
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39165634
If InStr({RateCode1.Description},"$") > 0 then
    If UBound(Split({RateCode1.Description},"$") > 1 then
        SPlit(Split({RateCode1.Description},"$")[2]," ")[1]
    Else
         0
Else
     "0"

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165662
It is coming up with the missing ) thing again.  I am sorry to be such a pain.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 total points
ID: 39165686
Missing the ) for UBound

If InStr({RateCode1.Description},"$") > 0 then
    If UBound(Split({RateCode1.Description},"$")) > 1 then
        SPlit(Split({RateCode1.Description},"$")[2]," ")[1]
    Else
         0
Else
     "0"
0
 

Author Comment

by:spudmcc
ID: 39165783
Came back "string is required"
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 39165791
Put " " around the 0

mlmcc
0
 

Author Comment

by:spudmcc
ID: 39165801
subscript error again.
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 39167485
I'm not sure what's causing the error, but I've added a couple more checks.  You never answered mlmcc's question about the field being null, so I added a check for that.  And you would get a subscript error if the only "$" happened to be the last character in the field, so I added a check for that.

If not IsNull ({RateCode1.Description}) and
 InStr({RateCode1.Description},"$") > 0 and
 InStr({RateCode1.Description},"$") < Length ({RateCode1.Description}) then
    Split(Split({RateCode1.Description},"$")[2]," ")[1]
Else
    "0"


 FWIW, I don't think the UBound > 1 check is necessary.  If the field contains a "$", UBound should be at least 2.  So I left that check out.

 James
0
 

Author Comment

by:spudmcc
ID: 39170936
Subscript error is now gone.  Sorry I thought I did answer the null question.  Yes, there might be a situation where you will not have any $ or decimal.  There are some instances where you don't have a $ but have a decimal.
0
 
LVL 35

Expert Comment

by:James0628
ID: 39171259
So, are you getting the results that you need now?  You've been trying a number of different formulas and I don't know where things stand now.

 James
0
 

Author Closing Comment

by:spudmcc
ID: 39171519
Thank you to both of you.  I realize this was above and beyond what might be able to be accomplished by Crystal or any other program for that matter.  The data is just not structured in a good format that one solution works.  

I hope splitting the points is a fair solution to both of you.  

Thanks again for all of your help, patience and knowledge.  

A
0
 
LVL 35

Expert Comment

by:James0628
ID: 39174186
The first thing with something like this is to come up with some solid rules for identifying the part(s) of the string that you want to extract.  If you can't do that, there's only so much that you can do, and truly reliable results may not be possible.

 James
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question