# Separating digits of number in each cell?

#### ExxcelNoob

##### Board Regular

Hello everyone, as you can see in the picture;

it is possible to separate numbers from Column A into each column of B and C?

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi,

If all numbers is in this format try:
in B1 = Left(A1,3)
in C1 = Right(A1,3)

Use either the "text-to-column" in the data tab, or use left() and right().

Wowww thank you everyone for your help!

Hi,

If all numbers is in this format try:
in B1 = Left(A1,3)
in C1 = Right(A1,3)

Use either the "text-to-column" in the data tab, or use left() and right().

WAIT! what if I have 72-120 in A1, also what if I have 1-2 in A1?

WAIT! what if I have 72-120 in A1?

Unless you are going to change values in Column A, your best bet is to use the Text-To-Columns functionality that Excel provides. Not sure of your version, but if XL2003 its on the Data menu item and if XL2007 (and I guess XL2010 as well... don't have that version installed) is on the Data tab. Select Column A, activate the Text-To-Columns dialog, select Delimited on Step 1 of 3 page and click Next... put a check mark in the checkbox labeled "Other" and put a dash in the field next to it, then click Next... type \$B\$1 in the Destination field and click Finish.

If you need a formula solution, then...

=LEFT(A1,FIND("-",A1&"-")-1)
=MID(A1,FIND("-",A1&"-")+1,9)

where the 9 assumes you will never have more than 9 characters following that dash. By the way, for future questions you may ask... if you data is not all consistent in "shape", give us examples that show the differences so we can give you the best answer for what you actually have to work with. In this case, showing us 3 examples with 3 characters before the dash does not indicate to us that there might only be 2 characters in front of the dash (or, perhaps, more than 3).

Last edited:
Unless you are going to change values in Column A, your best bet is to use the Text-To-Columns functionality that Excel provides. Not sure of your version, but if XL2003 its on the Data menu item and if XL2007 (and I guess XL2010 as well... don't have that version installed) is on the Data tab. Select Column A, activate the Text-To-Columns dialog, select Delimited on Step 1 of 3 page and click Next... put a check mark in the checkbox labeled "Other" and put a dash in the field next to it, then click Next... type \$B\$1 in the Destination field and click Finish.

If you need a formula solution, then...

=LEFT(A1,FIND("-",A1&"-")-1)
=MID(A1,FIND("-",A1&"-")+1,9)

where the 9 assumes you will never have more than 9 characters following that dash. By the way, for future questions you may ask... if you data is not all consistent in "shape", give us examples that show the differences so we can give you the best answer for what you actually have to work with. In this case, showing us 3 examples with 3 characters before the dash does not indicate to us that there might only be 2 characters in front of the dash (or, perhaps, more than 3).

OK lol thank you everyone, i'm so noob at this. THANK YOU !!

Hello everyone, as you can see in the picture;

it is possible to separate numbers from Column A into each column of B and C?
Here are my offerings.

Book1
ABC
1123-012123012
2123-456123456
3123-789123789
Sheet1

This formula entered in B1 and copied down:

=LEFT(A1,FIND("-",A1)-1)

This formula entered in C1 and copied down:

=MID(A1,LEN(B1)+2,15)

thank you

Replies
8
Views
153
Replies
4
Views
173
Replies
3
Views
268
Replies
3
Views
149
Replies
0
Views
175

1,196,329
Messages
6,014,678
Members
441,835
Latest member
rthomas268

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back