• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

Extract parts of a text field

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
spudmcc
Asked:
spudmcc
  • 13
  • 9
  • 3
2 Solutions
 
mlmccCommented:
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
 
spudmccAuthor Commented:
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
 
spudmccAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
mlmccCommented:
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
 
spudmccAuthor Commented:
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
 
mlmccCommented:
Which formula?

Can you copy it here?

mlmcc
0
 
spudmccAuthor Commented:
SPlit(Split({RateCode1.Description},"$")[2]),"")[1]
0
 
mlmccCommented:
Try

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

There was a ) after [2]

mlmcc
0
 
spudmccAuthor Commented:
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
 
mlmccCommented:
Can the field be NULL or not have the $?

mlmcc
0
 
spudmccAuthor Commented:
Good question...when I went back and looked at the raw data some don't have a $.
0
 
mlmccCommented:
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
 
spudmccAuthor Commented:
Bos, White Oak, Philly SatSun
Bos, White Oak, Philly FriToSu
Boston\Whiteoka\Philly Sat&Sun
BWPA00012wkMontoSun5.31/wk
0
 
spudmccAuthor Commented:
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
 
mlmccCommented:
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
 
spudmccAuthor Commented:
It is coming up with the missing ) thing again.  I am sorry to be such a pain.
0
 
mlmccCommented:
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
 
spudmccAuthor Commented:
Came back "string is required"
0
 
mlmccCommented:
Put " " around the 0

mlmcc
0
 
spudmccAuthor Commented:
subscript error again.
0
 
James0628Commented:
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
 
spudmccAuthor Commented:
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
 
James0628Commented:
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
 
spudmccAuthor Commented:
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
 
James0628Commented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 13
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now