Formula that works like text-to-column

[Guest]

I have a cell that looks like this "123, 456, 789, 234". Is there a formula
that I can use that to seperate them instead of doing text-to-column?

 

[Pete_UK]

You could use a combination of LEFT, MID and RIGHT functions, for
example if your text is in A1, then:

B1: =LEFT(A1,3)
C1: =MID(A1,6,3)
D1: =MID(A1,11,3)
E1: =RIGHT(A1,3)

These can then be copied down the columns.

Hope this helps.

Pete

 

[Guest]

Is there any way to do that in a single cell?

You could use a combination of LEFT, MID and RIGHT functions, for
example if your text is in A1, then:

B1: =LEFT(A1,3)
C1: =MID(A1,6,3)
D1: =MID(A1,11,3)
E1: =RIGHT(A1,3)

These can then be copied down the columns.

Hope this helps.

Pete

 

[Pete_UK]

But haven't you already got the values joined together in a single
cell, and you want to split them out? That's what you would normally
use Text-to-columns for, and that's what I thought you wanted to do !!

Pete

 

[Guest]

Sorry. Let me clarify. Cell A1 has "123, 456, 789, 234". The method you
suggested requires me to input four formulas in four cells to get the result
I want. Is there a formula (array formula maybe?) I can input in cell B1 that
can get me the same result as the four formulas you have suggested i.e. B1 =
123, C1 = 456, D1 = 789 and E1 = 234?

 

[David Biddulph]

You can't put anything in E1 by using a formula in B1, but if you want to
put the same formula in B1 to E1, use =MID($A1,5*COLUMN()-9,3)

 

[Guest]

Thanks for the answers. Whilst the question is not answered, they give me
ideas on how to solve my problem.