Excel gurus!?

Question

Ok, got one for ya. I want to take multiple text items that are in one cell seperated by a comma and have them be split into a different cells somewhere else on the spreadsheet.

heres the catch though, i need it to be able to update itself, so Text to Column doesnt work.

if i have "car , plane, boat" in A1 and i have those three split into B1:B3, i need to be able to go back later and change plane to helicopter and have B2 automatically change.

guess its kinda like a reverse concancacancnantnantanc-whatever

is this do-able?

Answer 1

Yes, you can do it. But it's not easy.

It's a matter of finding the commas, and then finding the words before, between, and after the commas:

1) find the commas (the location of the first comma in F1, the second in G1, and so on):
in F1: =IF(ISERROR(SEARCH(",",A1)), 0, SEARCH(",",A1))
in G1: =IF(F1>0, IF(ISERROR(SEARCH(",", $A1,F1+1)), 0, SEARCH(",",$A1,F1+1)),0)
in H1: =IF(G1>0, IF(ISERROR(SEARCH(",", $A1, G1+1)), 0, SEARCH(",",$A1,G1+1)),0)
in I1: =IF(H1>0, IF(ISERROR(SEARCH(",", $A1, H1+1)), 0, SEARCH(",",$A1,H1+1)),0)

2) Now that you know where the commas are, you can find the words:
in B1: =IF(F1=0,A1,LEFT(A1,F1-1))
in B2: =IF(F1=0,"", IF(G1=0, MID($A1, F1+1, LEN($A1)-F1), MID($A1,F1+1,G1-F1-1)))
in B3: =IF(G1=0,"", IF(H1=0, MID($A1,G1+1, LEN($A1)-G1), MID($A1,G1+1,H1-G1-1)))
in B4: =IF(H1=0,"", IF(I1=0, MID($A1,H1+1, LEN($A1)-H1), MID($A1,H1+1,I1-H1-1)))

This will work for anything from one word (no comma), up to 4 words (separated by 3 commas). If you need to be able to have more than 4 words and need help with that, let me know.

Good luck.
.

Answer 2

You can also try the 'left' , mid, and right features.... It's in function under Text area..... that would say "go to this cell (i.e. the merged cell) and give me the data "counting so many cells from the left"(mid,right)..... If you had carplaneboat in a cell and you used Left... Left=3 and it would give you car.

It would present a problem when you had different length cell data though. Excel does not recognize that the merged data in the cell has been separated.... there is no association as far as I know. It would work OK if your data for that particular line was always the same.

I think if you play with that feature - you'd be able to finagle something.

Answer 3

The only way I know to do it would be to use a Left, mid, and right function, but it's one that can't be copied downward if the number of characters are different in the subsequent cells.

So in your example, you would enter in cell B1: =Left(A1,3)
The function says to return the first 3 characters from cell A1.

Enter in cell B2: =Mid(A1,6,5)
This function says to return the 5 characters, from a starting point of 6 characters from the left.

Enter in cell B3: =Right(A1,4)

Answer 4

Yes.
Type these formula in the 3 cells (B1 to B3)

B1=LEFT(A1,SEARCH(",",A1)-1)
(Finds the first "," comma in the text string and gets all the letters to the left of it except "," . Thats why there is a -1.)

B2= LEFT(RIGHT(A1,LEN(A1)-LEN(D1)-1),SEARCH(",",RIGHT(A1,LEN(A1)-LEN(D1)-1))-1)
(Here we first leave out the text we have already taken in the 1st formula by using "right" & "len" functions and then do what we did in the 1st one)

B2=LEFT(RIGHT(A1,LEN(A1)-LEN(D1)-LEN(D2)-2),LEN(RIGHT(A1,LEN(A1)-LEN(D1)-LEN(D2)-2)))

(same as 2nd one - leaving out the 2 texts we have already taken. But here since we dont have commas any more, we cant use the "search" function.

I assumed there is no space between texts and commas. Even if there is space, i am sure you can do it

======================================
I did my formulas in the column "D". I forgot to tell you. I know you must have noticed it. So change them

Answer 5

nope.... you can't have three items in one cell and expect another cell pick out any 1 from 3

to find out the best way to do your calculation (text) is to join a excel forum. where the experts will tell you how to make a sheet that will do as you expect,

so do a web search for :-

excel+user+forums see what you come up with

try the links below

Answer 6

uhh i dont think so. this sounds kind of confusing.