• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Splliting cell contents

bobhc

bobhc

Excel Ninja
Good day All

I have the following in cell A3


“ROLLGUDIE-234-B25R”, this is a reference to a mill cassette spare part, to satisfy the whims of the noideawhatiamdoingsoiwillblamesomeoneelse, management I need to break the part code in to three so I have the following.

I have done some site searching and have come up with this result


=LEFT(A3,SEARCH(“-“,A3)-1…….to start a the “R” and search up to the “-“ and then back one to the “E”.


=MID(A3,SEARCH(“-“,A3)+1,3…….to search up to the “-“ add one character to take up to the “2”, and take the “3” characters


=RIGHT(A3,SEARCH(“-“,A3)+1,4……. to search up to the “-“ add one character to take back up to the “B”, and take the “4” characters.


My question is there are bloody thousands of spare parts in a rolling mill, and we have two mills, I have tried the above on a few cells and to me (which is not saying much) the formulas “seem” to be working. I would appreciate it if someone could give comment before I apply this undeniable work of art to 78 spread sheets.

I have put my thinking after each formula so that you can point out the error of my thoughts.

If some of the spare parts have more than 3 mid or more than 4 right I will end up with a total screwed up mess and one smug looking manager, how to I take account of this, do I just put in a large number say 25 to capture all.
 
Hi


How about a UDF, from http://chandoo.org/forums/topic/need-predefined-formula-for-text-to-column#post-32161 and then


Code: 
=TextToColumn("Old-Dog-New-Trick-Wife-Operated-But-o-But-HC","-")


Old	Dog	New	Trick	Wife	Operated	But	o	But	HC


Regards,

Deb
 
Good day Debraj Roy

I did see some ideas on UDF's while searching and to be honest i just got the opinion that to keep it simple three formulas in their respective cells copyed down would do the job.
 
Hi bobhc!


I have been working with data related to spare parts. I have found following points beneficial:


1. Create a backup first! That is the most important thing whenever you are experimenting with a file! Lolz


2. I assume that your set on diff sheets will have similar headers/layout, if not make one.


3. Merge them and create a mega sheet.


4. Create a pivot table with parts no. listing in the left most (row labels) area, this will help you see whether all parts no.s have three parts or more then that.


5. If just three you can come down to the last row by pressing ctrl+down key, enter a dot in that cell right to the last one and apply this to the all column on the right.


6. Go to the top by pressing ctrl+up and apply formula by selecting entire column shift+ctrl+down key, press f2, enter, do this for rest of column.


I hope your work load will reduce.


Or ask Debraj to write a macro for you :D
 
Your first two formulas look okay. The RIGHT function is not going to work as you have it written, as the SEARCH function is going to pick up the first dash.

Are you always guaranteed about the length of the parts? If not, here's some more robust formulas that can handle various sizes, in case the middle or right portion can grow/shrink.


=LEFT(A3,SEARCH("-",A3)-1)

=TRIM(MID(SUBSTITUTE(A3,"-",REPT(" ",999),2),FIND("-",A3)+1,999))

=TRIM(RIGHT(SUBSTITUTE(A3,"-",REPT(" ",999)),999))
 
Faseeh


My thanks, if it where the case that it was just me who was using the data then pivot is the way I would have gone, the 78 sheets are all the same in column number as they all contain the same header but row numbers are different due to way the spares are allocated to different sections of the mills(one sheet, one section) from furnaces too overhead cranes too cooling beds and all the rest, these sheets are maintained by the stores staff, and believe me when I say they look upon Excel as the work of the devil. They do not like change, so I am trying to put these formulas into three new columns that I will create. The new columns will just update when the main part code is entered or changed. As for the numbers in the codes, we have started to look at buying in parts from Italy and using the numbering that the America companies that supply us use instead of shortening them, as these companies use more than 3/4.


Luke M Thank you


This is what I am looking for, it took me awhile to understand the formulas I had so I think I will get a cup of tea and start trying to understand TRIM, MID, SUBSTITUTE, REPT. I think it is a good idea to have some understanding of the code before using it. Again my thanks.
 
Good day/evening/morning Luke M

I have finished running your formulas on the 78 spread sheets with thousands of rows, everything worked out perfectly.

I ran my code as well on a backup of some of the work sheets and they worked, after I had changed the range from 4 to 3 in the RIGHT function :)

The beauty of your code is that it takes care of the stock codes with varying lengths of data but more importantly it takes care of leading and trailing spaces and that is where most of the time is taken with my coding, it does not remove spaces but it sure as hell throws up some errors to tell you that some numb nut who is entering the data has a knot in his spinal cord and their thought process go straight to their arse.

I give you my thanks, workbook saved ready to take to work on Monday, easy day, give to the manager about 3:30 and leave him scratching his head in wonderment :) I am going to have one or two malts, thanks.
 
@b(ut)ob(ut)hc

Hi!

You sounded me like Jim Carrey as Truman Burbank in The Truman Show :p

Join you with the malts, of course.

Regards!

PS: BTW, good evening, old English sheepdog!
 
Hi Luke M and all

Boss rang to say how long I would be with the alterations, when I said I had done it he was in my office quicker than a scoulded cat up a tree. The look on his face as I showed him the alterd spread sheet after spread sheet was priceless.

All I can do is pass on to you my thanks for all the help
 
You must log in or register to reply here.
Back
Top