Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel+training - Splitting text/data in one cell to two of more cells
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
excel+training - Splitting text/data in one cell to two of more cells
Resolved · Low Priority · Version Standard
Reply Wed 30 Jan 2008, 10:36Delegate Robert said...
Robert has attended:
Excel Intermediate course
Excel Advanced course
Splitting text/data in one cell to two of more cells
I am trying to work with data that has been exported from another application into Excel. Mostly this transfers well but in one case text/data appears on the same line and I would like to divide it to then manipulate it in Excel in separate columns.
This particular example is a line with hardware asset numbers and serial numbers appearing together in the same cell in Excel (asset :29635 serial :L3W8506). I would like to have these divided into separate cells in adjacent columns. Clearly were it just one line I could cut and paste manually but there are over 150. Surely there must be a better way (other than dividing at source and re-importing)...?
For upcoming training course dates see: Pricing & availability
Reply Wed 30 Jan 2008, 15:09Delegate Paul said...
RE: Splitting text/data in one cell to two of more cells
Hi Robert.
Assuming that your asset numbers are always 5 digits long, you can do one of two things. Extract just the number or extract 'asset :29635'
you can use the mid() function to extract the number.
=mid(A1,8,5) would look at cell A1 and display what is in the 8th, 9th, 10th, 11th and 12th place holdings of that cell, so this would return 29635
otherwise you could try this:
=left(A1,12) would pull out the first 12 place holdings of cell A1, so this would return 'asset :29635' (without the speechmarks, of course)
Then, use the mid() or right() functions to extract the serial number too, assuming your serial number is always 7 characters long.
Hope this helps.
Anything else, just ask!!
Paul
|
|