excel query- how to make two strings from one, e.g how to split cell A1 fig 0054398700001451.00 into A1 & B1

Question

i want fig 00543987000 in cell A1 & fig 01451.00 in cell B1. pl consider that i have more fig of this type in cell A2, A3 etc which are also have to be splitted into two cells.

it is always a 27 digits fig starting with two zeros and last eight digits are like 01451.00 or 15671.00 or 43567.00 etc and these last 8 digits needs to be seperated

and thanks to rad poe as he is always answering my question though not gwtting the best. thanks again to rad poe

after split i don't want to keep the original one. i tried split & data split in to cell B & cell C but when i try to delete the original column cell A, the data splitted data of B & C gets corrupted & diff signs appear in that cells

Radpoe- u r always trying to help me my good net friend, thanks but one request ur answer can get u best if u answer more than to the point answer assuming what else asker may be wanting, qwertykph- u are again getting best as ur answer are easy to understand though xlman is also right and i am sure to get more knowledge from all of u in future also. thanks to everyone.

Answer 1

I think you'd be better off using "Text to columns". Especially since you stated the first number is always 27 characters.

1) Highlight column that contains your long numbers.
2) Click Data, choose "Text to columns".
3) Choose "Fixed width", click next
4) Click on position where you want column break. A vertical line will appear. Click next.
5) Choose format options for each column. If you want to keep the leading zeroes, format column as text. Choose destination cell for data. (I recommend new cells elsewhere on the sheet.) Click Finish.
6) Enjoy you new columnar data!

Hope this helps,
Regards,
QwertyKPH @ Yahoo!

Answer 2

Follow these steps carefully
1- In cell B1 type this function
=left(A1,27)
2- in C1 type this
=mid(A1,28,10)
3- Copy and paste these functions into the cells below
4- Select these columns B:C
5- goto Edit > Copy
6- goto Edit > Paste Special (Without changing the selection
7- Now In this window select "Values"
8- Ok
9- Delete the Column A now
10- Good luck
11- Contact me for more info

Read my profile, I am XLMan

Answer 3

We meet again, Sam I am :)

To answer your question, it all depends on how you need to split them up:
If it is a constant number of digits, then:
=LEFT(A1, 12)
will result in the left-most 12 digits

Please elaborate on how you differentiate between the figures.

CONTINUE:
Sam, when you get the results you wanted in seperate columns, copy those columns and paste them to the same location, using PASTE SPECIAL > Values.

This will result in the turning of the active formulas into values, hence, independent from the original.

Hope that grants me the best answer :)

Answer 4

1. format the cell as Text.
2. on another cell, insert function LEFT, chose the cell and the number of characters from left of the cell to appear.
3. On another cell, insert function RIGHT, chose the cell and the number of charecters from right of the cell to appear.
e.g. Left(B4,11)
Right(B4,8)

Answer 5

i dunno