Excel: Delimited text > convert to columns

[dice1976]

Hello Excel Experts,

I have been trying this a number of ways and I can not find a way (if it's possible) to make it work.

I have a | delimited text- i.e. 1|2|3|4|5|6

and I would like to take this text and paste it into a cell, A1

Upon pasting it into a cell (if macro is used, that is fine) I would like to remove the | delimiter, and have it paste the values in cell B1:B6

So, to sum up:
Cell A1= 1|2|3|4|5|6
when cell A1 contains the data (1|2|3|4|5|6)
B1 = 1
B2 = 2
B3 = 3
B4 = 4
B5 = 5
B6 = 6

Thanks in adavance!

 

[Oaktree]

Welcome to the board.

Here's one way:

B1 =LEFT(A1,FIND("|",A1)-1)
B2 =LEFT(REPLACE($A$1&"|",1,SUMPRODUCT(LEN($B$1:B1))+ROW()-1,""),FIND("|",REPLACE($A$1&"|",1,SUMPRODUCT(LEN($B$1:B1))+ROW()-1,""))-1)

Copy B2 down through B6.

 

[dice1976]

Welcome to the board.

Here's one way:

B1 =LEFT(A1,FIND("|",A1)-1)
B2 =LEFT(REPLACE($A$1&"|",1,SUMPRODUCT(LEN($B$1:B1))+ROW()-1,""),FIND("|",REPLACE($A$1&"|",1,SUMPRODUCT(LEN($B$1:B1))+ROW()-1,""))-1)

Copy B2 down through B6.

Hey Oaktree! Wow that is exactly what I was looking to do!! Very exciting to see it in action and work.

I have one follow up question which will be very simple actually.

I am trying to use your code/formula and I want to use it with my data in cell C1 but I need it to fill down from C3:C9.

I tried to copy and paste or insert rows in order to change the formula, I can see that it attempts to change it but it prints out some data improperly. Can you explain briefly how the formula works and show me what code I should be using if I want this to begin in cell C3?

Thanks again so much for your help. You don't know how long I've been working on trying to make something like this work.

 

[Oaktree]

Similar logic:

C3 =LEFT(C1,FIND("|",C1)-1)
C4=LEFT(REPLACE($C$1&"|",1,SUMPRODUCT(LEN($C$3:C3))+ROW()-3,""),FIND("|",REPLACE($C$1&"|",1,SUMPRODUCT(LEN($C$3:C3))+ROW()-3,""))-1)

Instead of ROW()-1, use ROW()-3

Try stepping through the formula (selecting part of it and pressing F9 to calculate only the highlighted part) to follow the logic.

In general terms, the first cell (C3 in this iteration) returns everything before the first "|" found in your string. The second cell replaces the first character of your string through the xth character in your string (where x is the combined length of the formula results above + the number of "|"s you should have [hence the ROW() - 3]) with nothing. Then, it finds the first "|" in this new, truncated string.

In other words: starting with 1|2|3|4|5|6
C3 = return everything up to the first "|" = "1"
C4 = truncate 1|2|3|4|5|6 to 2|3|4|5|6 then return everything up to the first | = "2"
Similar logic as C4 for the other cells.

 

[strategygamer]

Alternatively you can use the text to columns feature, then transpose the data to row based instead of column based.

 

[dice1976]

Wow... this is perfect! I appreciate all your help. And now I understand the logic which is even better. I couldn't figure out the -1 & -3 parts.. I get it now!

Similar logic:

C3 =LEFT(C1,FIND("|",C1)-1)
C4=LEFT(REPLACE($C$1&"|",1,SUMPRODUCT(LEN($C$3:C3))+ROW()-3,""),FIND("|",REPLACE($C$1&"|",1,SUMPRODUCT(LEN($C$3:C3))+ROW()-3,""))-1)

Instead of ROW()-1, use ROW()-3

Try stepping through the formula (selecting part of it and pressing F9 to calculate only the highlighted part) to follow the logic.

In general terms, the first cell (C3 in this iteration) returns everything before the first "|" found in your string. The second cell replaces the first character of your string through the xth character in your string (where x is the combined length of the formula results above + the number of "|"s you should have [hence the ROW() - 3]) with nothing. Then, it finds the first "|" in this new, truncated string.

In other words: starting with 1|2|3|4|5|6
C3 = return everything up to the first "|" = "1"
C4 = truncate 1|2|3|4|5|6 to 2|3|4|5|6 then return everything up to the first | = "2"
Similar logic as C4 for the other cells.