How to split the contents of a cell between two cells.

[Colin Hayes]

Hi

I have a string of 5 characters in cell A1. I need to take off the final
2 characters from the string and place them in A2 , leaving the string
split 3 and 2 between the 2 cells.

Is this possible via the formula bar?

Grateful for any help.


Drno

 

[Duke Carey]

You'll need 2 new cells: one to hold the 3 characters & one to hold the 2
characters.

In A2 use the formula
=right(trim(A1),2)

In the cell where you want the 3 character string use the formula
=left(trim(A1),3)

If you want to end with just the two result cells, copy them, and use
Edit>Paste Special Values to paste the results asVALUES over top the
formulas. Then you can delete A1

 

[CLR]

One way is to put =RIGHT(A1,2) a2, and then do Copy > PasteSpecial > Values
on that cell.
Then click on A1 and do Data > TextToColumns > FixedWidth > Next, and set
the column line between the third and fourth characters, > Next > then
highlight the right column with the two characters in it and check "Do not
import column " > Finish

Vaya con Dios,
Chuck, CABGx3

 

[Kevin M]

Hi Colin, you can use Data > Text To Columns. This will split it without
having to enter any formulas and then having to paste special the values from
those formulas. On the first page of the wizard make sure you choose "Fixed
Width", click next and then you can set the column break manually, once
finished this will add 2 columns with the now parsed data, delete the first
column and you're done.

HTH,
Kevin M

 

[Colin Hayes]

Hi All

Thanks for your help on this - you've solved my problem. I'm grateful
for your expertise and interest.


Best Wishes

Drno