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

Separate String into two columns

I have a large report that has a description of some studs. It is currently a string. The description looks like this:

1 1/2-8 X 9-1/4 B7 STUD
1 1/2-8 X 9-1/4 B7 STUD W/2 2H NUTS
1 1/2-8 X 9-3/4 B7 STUD
1 1/4-7 X 18" B7 STUD HDG W/2NUTS & 2 L/
1 1/4-7 X 26" B7 STUD HDG
1 1/2-6 X 50" B7 STUD DRILLED B.E. HDG
1 1/2-6 X 5-1/2 B7 STUD
1-8 X 8 1/2 B7 STUD W/2 2H NUTS XYLAN BL
1-8 X 8 B7 STUD

I need the description broken into two columns.  The first column would be the diameter, which is the first part of the description before the "X".  I have a formual for that already. I need help with the second column. I need the length of the stud (the part after the "X")  And I need that formatted into a number so that I can do some calculations with it.

I failed to mention that when I said I needed a number format, I need it to display any fraction as a decimal too.  So the 9-1/4 would show as 9.250

I was able to get some of the data converted to the way I wanted but I'm having issues for the ones that have a fraction in them or have the " sign showing inches.  In other words, if it was a whole number, I could get it to work....but I'm lost if it isn't a whole number.

0
ITworks
Asked:
ITworks
  • 6
  • 4
1 Solution
 
ITworksAuthor Commented:
I failed to mention that when I said I needed a number format, I need it to display any fraction as a decimal too.  So the 9-1/4 would show as 9.250

I was able to get some of the data converted to the way I wanted but I'm having issues for the ones that have a fraction in them or have the " sign showing inches.  In other words, if it was a whole number, I could get it to work....but I'm lost if it isn't a whole number.

0
 
mlmccCommented:
From your other question you got this as the second column

Formula for 2nd column:
Replace(Split({your_field}," X ")[2]," B7 STUD","")

Try - This is in resoonse to the question today that didn't include the requirement to convert fractions

Formula for 2nd column:
Trim(Replace(Split({your_field}," X ")[2]," B7 STUD*",""))

If that doesn't work
Trim(Split(Split({your_field}," X ")[2]," B7 STUD","")[1])

If you can have 5-1/2 as a length, you won't be able to total it directly.  The numbers will have to be converted.
For the conversion, Will the format always be N-N/N or can you have N N/N
where N are any digits like 16-3/8

How large a denominator?

mlmcc


0
 
ITworksAuthor Commented:
Unfortunately some of the numbers will be N-N/N and N N/N.

The denominator will be a maximum of two characters long
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mlmccCommented:
I think this will work

mlmcc
Local StringVar strForValue;
Local StringVar Array strLengthList;
Local StringVar Array strFractionList;

strForValue := Trim(Split(Split({YOUR FIELD}," X ")[2]," B7 STUD")[1]);

strForValue := Replace(strForValue,'"','');
strForValue := Replace(strForValue,'-',' ');

strLengthList := Split(strForValue,' ');
If UBound(strLengthList) = 1 then
   Val(strLengthList[1])
Else
(   
   strFractionList := Split(strLengthList[2],'/');
   Val(strLengthList[1]) + Val(strFractionList[1]) / Val(strFractionList[2]);
)

Open in new window

0
 
mlmccCommented:
If there are chances the data will take other formats or that the data may have bad values then there are some tests that can be added to ensure the values used in calculations are numeric strings.

mlmcc
0
 
ITworksAuthor Commented:
It'spossible for the format to be different.  This is a description of a part number in database.  People are supposed to adhere to some policies but since several people (30-40 people) have access to create the description there could be situations where someone enters the data differently.  That is why we sometimes see 5-1/2 and 5 1/2.  I can't go in and change this as there is already history on that part.
0
 
mlmccCommented:
I meant if there are other formats in use like
   5_1/2
   
Or if there were things like
   5 A1/R4

mlmcc
0
 
ITworksAuthor Commented:
NO.  Those types of formats are not used.
0
 
ITworksAuthor Commented:
WOW!     Great job!    Thanks so much for your help.
0
 
ITworksAuthor Commented:
Great job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now