Windows XP How to flip cell content

Speeedog

Speeedog

Joined
Apr 29, 2011
Messages
3
Reaction score
0
Hi, I'm new to the group and have a question. I have hundreds of cells with numbers such as the following in one cell (each line is in one cell). (excel 2003)

1.202.69 | 840 B-A-G-03112
840B-A-G-03114 | 1.202.72
1.202.90 | 840 B-A-G-03120
1.204.7 | 840 B-A-G-03131
840B-A-G-03134 | 1.204.18
1.204.87 | 840 B-A-G-03141

I need to flip the two numbers separated by | , so that the number that begins 840 appears first like this:

840 B-A-G-03112 | 1.202.69
840B-A-G-03114 | 1.202.72
840 B-A-G-03120 | 1.202.90
840 B-A-G-03131 | 1.204.7
840B-A-G-03134 | 1.204.18
840 B-A-G-03141 | 1.204.87



Thank you for any help you can give.
Speeedog
 
Last edited:
First, clear 3 columns to the right of your data.
Select the column, or select the cells you want to flip.
In 2003 choose data>text to columns. Select delimited and use the | for the delimeter.
This will create 2 columns from your data.
In the next column enter a formula like =concatenate(cell 1, "|", cell2) where 'cell' is where your data resides.
Fill down. Copy the result and paste special>Values to remove the formula.
 
Thank you for your help. I am at home now instead of work, so I am using Excel 2007, but was able to follow your instructions ok, but this is how the three columns ended up. I created the two columns just fine and entered the formula and then filled down and the third column ended up like the original data.

I must have done something wrong, but don't know what. I need all the numbers beginning with 840 to be the first number and then the "|" and then the other number Example the top row needs to be flipped to 840 B-A-G-03112 | 1.202.69. Some of the date is already as it needs to be, but the ones that begin like 1.202.691 needs to be flipped. I hope this makes sense.

This is how the third column ended up after I made the two columns:
1.202.69 | 840 B-A-G-03112
840B-A-G-03114 | 1.202.72
1.202.90 | 840 B-A-G-03120
1.204.7 | 840 B-A-G-03131
840B-A-G-03134 | 1.204.18
1.204.87 | 840 B-A-G-03141
 
Last edited:
It sounds like you need to sort the columns and only perform the text to columns and concatenate on the data that does not begin with 840.
 
Back
Top