Link to home
Create AccountLog in
Avatar of Hiroyuki Tamura
Hiroyuki TamuraFlag for United States of America

asked on

Excel 2010 question

How do I change the data  from

User generated image
to


User generated image
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Assuming data in cells B1 and C1, use the following formulae:

A1:  =B1
A2:  =MID($C$1,1,7)
A3:  =MID($C$1,9,7)
A4:  =MID($C$1,17,7)
A5:  =MID($C$1,25,7)
A6:  =MID($C$1,33,7)
A7:  =MID($C$1,41,7)
A8:  =MID($C$1,49,7)

You will no doubt notice the only difference for each formula is the number in the middle. The number represents the starting position for the next string to be extracted, the 7 is then the number of characters to extract. The starting point is incrementing by 8 each time because although your values are only 7 characters each, the invisible carriage return in the cell counts.

No doubt this is a very simplified version of the data so the above is going to need to be tweaked to allow for multiple lines and no doubt differing quantities of entries in column C.

A more accurate representation of the data in a file uploaded would help give a better answer.

Thanks
Rob H
ASKER CERTIFIED SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Hiroyuki Tamura

ASKER

thank you.