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

i want fig 00543987000 in cell A1 %26amp; 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.

Excel query- how to make two strings from one, e.g how to split cell A1 fig 0054398700001451.00 into A1 %26amp; B1
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!
Reply:You're most welcome Sam. I have been working in support-related IT for 12 years now. I tend to over-simplify answers and that usually give a good result. Maybe the steps were more detailed than you personally need, yet thi is a public exchange.

QwertyKPH @ Yahoo Report It
Reply: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 %26gt; 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 :)
Reply: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)
Reply: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 %26gt; Copy

6- goto Edit %26gt; 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
Reply:i dunno