splitting text in a cell..

[via135]

is there any way to split text content in a cell and copy it to another
location of cells. for eg:

Cell A1(text format) contains 123456789
and i want to split the content into three parts as 123, 456 & 789 and
copy the three values into b1,C1 & D1 respectively.

can anybody help me?

via135

 

[ScottO]

Have you tried the MID function? It should be able to give you what
you want.
Rgds,
ScottO

in message
|
| is there any way to split text content in a cell and copy it to
another
| location of cells. for eg:
|
| Cell A1(text format) contains 123456789
| and i want to split the content into three parts as 123, 456 & 789
and
| copy the three values into b1,C1 & D1 respectively.
|
| can anybody help me?
|
| via135
|
|
| --
| via135
| -------------------------------------------------------------------
-----
| via135's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=26725
| View this thread:
http://www.excelforum.com/showthread.php?threadid=492639
|

 

[via135]

hi..SCOTTO

i am able to get the result with the MID function. but the thing is, i
want to split one text string into 3 different strings and copy those 3
strings into different cells at one stroke using a formula..! can u help
me?!

regds
via135

 

[Ragdyer]

You'll need 3 formulas ... one in each "receiving" cell.

Do you want that?

In B1:
=LEFT(A1,3)

In C1:
=MID(A1,4,3)

In D1:
=RIGHT(A1,3)

You can also use TTC, if all your data is as you posted (equal length
strings), which will copy the separated data into 3 contiguous columns with
"one stroke".

Select your column of data, then:

<Data> <TextToColumns>
Click on "Fixed Width", then <Next>,

Click in the "Preview" window and place the break lines where you wish to
separate the data, then <Next>,

Click in the "Destination" box and enter the cell where you wish the "copy"
to start, say B1, then <Finish>.

Is that what you want?

Anything else will need code.

 

[via135]

thks RAGDYER!

TTC works fine for contiguous cells!
can u explain the code for copying into non-contiguous cells?

regds!

via135

 

[RagDyer]

Code is a little out of my league.

You could use the formulas I posted and enter them in the columns you wish
to use, then copy them down the columns.
This does however, leave you with formulas returning data, and not the
actual data itself.
You can then remove the formulas and leave the data behind by copying the
columns to themselves, and then "Paste Special", and check "Values".
Not exactly a "one shot" procedure.

You can also use TTC to copy to non-contiguous columns, but again, not a
"one shot" deal.
You'll have to do it 3 times, one for each column.

After you place the break lines and hit <Next>,
The first separation (column) is selected by default.
Hold down <Shift> and click in the second separation so that both are
selected.
Then click in "Do Not Import".
You'll see the headers change from General to Skip.
Now, enter your first target column in the "Destination" box, then <Finish>.
You'll need to do this twice again for the other 2 separations, skipping the
first and third, and then the last two.

If you find both of these procedures unappealing, you can post to the
programming group, and see if they can help you.