x
Solved

# Excel 2010 question

Posted on 2016-10-17
Medium Priority
99 Views
How do I change the data  from

to

0
Question by:Hiroyuki Tamura
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 33

Expert Comment

ID: 41846899
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
0

LVL 33

Accepted Solution

Rob Henson earned 2000 total points
ID: 41846915
Assuming you have multiple entries, the following will change the entry into a "pipe symbol" separated string:

=B1&CHAR(124)&SUBSTITUTE(C1,CHAR(10),CHAR(124))

You can then copy and paste as values and use Text to columns using the pipe separator as a delimiter. this will then put the values into separate columns which can then be copied and pasted, transposed into rows.
0

Author Comment

ID: 41853978
thank you.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month15 days, 8 hours left to enroll