Formula that works like text-to-column
 

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?

Formula that works like text-to-column
 

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

On Mar 8, 12:45 pm, linglc wrote:
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?

Formula that works like text-to-column
 

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

"Pete_UK" wrote:

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

On Mar 8, 12:45 pm, linglc wrote:
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?

Formula that works like text-to-column
 

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

On Mar 8, 1:17 pm, linglc wrote:
Is there any way to do that in a single cell?



"Pete_UK" wrote:
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

On Mar 8, 12:45 pm, linglc wrote:
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?- Hide quoted text -

- Show quoted text -

Formula that works like text-to-column
 

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?


"Pete_UK" wrote:

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

On Mar 8, 1:17 pm, linglc wrote:
Is there any way to do that in a single cell?



"Pete_UK" wrote:
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

On Mar 8, 12:45 pm, linglc wrote:
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?- Hide quoted text -

- Show quoted text -

Formula that works like text-to-column
 

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)
--
David Biddulph

"linglc" wrote in message
...
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?

"Pete_UK" wrote:

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 !!

On Mar 8, 1:17 pm, linglc wrote:
Is there any way to do that in a single cell?

"Pete_UK" wrote:
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.

On Mar 8, 12:45 pm, linglc wrote:
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?-

Formula that works like text-to-column
 

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


"David Biddulph" wrote:

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)
--
David Biddulph

"linglc" wrote in message
...
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?

"Pete_UK" wrote:

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 !!

On Mar 8, 1:17 pm, linglc wrote:
Is there any way to do that in a single cell?

"Pete_UK" wrote:
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.

On Mar 8, 12:45 pm, linglc wrote:
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?-