Solved

Extract parts of a text field

Posted on 2013-05-13
25
263 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 100

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
 
LVL 100

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 100

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 100

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 100

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 100

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 100

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 100

Assisted Solution

by:mlmcc
mlmcc earned 250 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 100

Expert Comment

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

mlmcc
0
 

Author Comment

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

Accepted Solution

by:
James0628 earned 250 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 34

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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now