- NEW!
by Cyanotic Wasp on November 18th, 2009
Brian's answer works for codes of exactly the same length (6 characters always).
If the code lengths vary, then you can use this formula for the MIDDLE value (the only one that would vary):
=mid( E18, 2, len( E18) - 2)
(Actually, this code works regardless of whether or not the code lengths vary, so you could substitute it for Brian's suggestion with no ill effects.)
by Brian I on November 18th, 2009
If all your codes have the same number of digits this is very straightforward. The following formulas assume that your original code is in cell E18.
In cell F18 enter the formula =LEFT(E18,1)
In cell G18 enter the formula =MID(E18,2,4)
In cell H18 enter the formula =RIGHT(E18,1)
It becomes more complicated if the codes have different numbers of digits.
by Brian on November 18th, 2009
Click on cell or column info is in. Go to Data on your tool bar then click "Text to Columns". Click radio button next to "Fixed with", then next. Click on the line where you want the breaks to be, then next. Confirm what you want to export. You can choose here to not export a part of the text you are splitting. Then click next. You must be sure that the cell to the right are clear. So if you are creating 3 columns you need 2 columns to the right open. If not, items in cells to the right will be deleted. You can add columns prior to splitting cell.
Is anyone on here experienced with Microsoft Excel? I need a favor, please.
by NiCkIzBacK on August 24th, 2011
| 1 person likes this
Could someone give me a good definition of VLOOKUP in Excel?
by slimNanny on August 13th, 2011
| 1 person likes this
I need a formula in Excel that does the following..
If the value in cell A1=X, then multiply cell B1 by 5..if A1=Y, then multiply B1 by 8.
by leslie2413 on September 1st, 2011
| 2 people like this
No matter what cells I select, the freeze panes option always selects the first 15 rows to freeze. How do I fix this in Excel 2010?
by Nickname on October 8th, 2011
| 1 person likes this
In Microsoft Excel I need 25 hours to be displayed but when I enter 25:15 (hours:minute) it shows 1:15. Any ideas on how to fix this?
by One mans opinion on August 10th, 2011
| 1 person likes this
You're reading I have an excel column. Each cell contains a code similar to 16203K - I want to split the cell into 3 seperate columns to seperate the first and last characters - i.e 1 / 6203 / K Is this feasible? I am not technically gifted so simple answer appreciated
Your favorite Q&A site is on Facebook! Become a fan to connect with the ever-growing community of Answerbaggers, share your thoughts and find out what Answerbag's up to next!
Comments
I'll have to try to remember that. I was going to research it if Grim had come back saying his code numbers were of variable lengths. Perhaps it's a pity he didn't, because I would have a better chance of remembering it if I had had to find out for myself. :-)
by Brian I on November 18th, 2009
I do this stuff so often in parsing email addresses, part number codes and various other account codes and other text of varying lengths that it's almost second nature to me now.
My favorites are working with things like email addresses, where you get to incorporate a FIND( x, y) variable, too. So to get, for example, all of the domain names from a list of email addresses starting at E22, you'd use:
=RIGHT( E22, LEN( E22) - FIND( "@", E22))
Fun stuff, text manipulation.
by Cyanotic Wasp on November 18th, 2009