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)...?
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
