Separating digits of number in each cell?

[ExxcelNoob]

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?

 

[Ingolf]

Hi,

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

 

[pplstuff]

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

 

[ExxcelNoob]

Wowww thank you everyone for your help!

 

[ExxcelNoob]

ADVERTISEMENT

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?

I don't want the dash sign include it.

 

[Rick Rothstein]

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

I don't want the dash sign include it.

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).

 

[ExxcelNoob]

ADVERTISEMENT

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 !!

 

[T. Valko]

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
	A	B	C
1	123-012	123	012
2	123-456	123	456
3	123-789	123	789
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)

 

[ExxcelNoob]

thank you

 

[Scott Huish]

Here's mine:

Excel Workbook
	A	B	C
1	1-1	1	1
2	121-702	121	702
3	100034-5678	100034	5678
Sheet1