Seperating sections of a string of values
Posted by Simon Scott on September 28, 2000 9:32 AM
OK here is the scenario. I have a string of values in column A, lets say 10 values: AB12345XYZ
I need a function that can write in Column B and it will take the first two characters, AB, and put them into Column B and then I can use the same funtion in Column C to take the next 5 characters and finally use the same function in Column D and take the last 3 Characters.
Any ideas what the function is?
Thanks
Simon
Posted by Ben on September 28, 2000 11:06 AM
It's not a function, but Text to Columns would work well for what you want. Make sure you have some empty columns to the right of the column you'll be parsing, then select that column and choose Text to Columns from the Data menu. Choose "fixed width" and you'll be asked to insert the column dividers where you want them. You can put in as many as you want, but remember that you'll never an empty column ready for each divider you insert.
-Ben
Posted by andy on September 28, 2000 12:46 PM
Use the LEFT function to take the left two characters from the string. There is also a right function to take the rest of the string. If the string length varies, then you could do something like RIGHT(C4,LEN(C5)-2). This would take all but the first two characters. If the results from the RIGHT function are numbers, you can convert those to numbers that Excel can do arithmetic on by using the VALUE function. You can also take a internal piece of a text entry with the MID function. Try to use the LEN function instead of guessing a standard length so your forumla is more general.
