# text to columns

#### uzbeklana

##### New Member
I am trying to sort out some information by separating a column which contains numbers like 1-xxx and xxxx. Most of the numbers that don't have a "1-" in front of it have three zero's and then another number (e.g. 00000, 00009). I want to retain those 0's. I have the column in "text" form so it will do so. BUT, when I do the text to columns with the "-" as the delimited character, the 00000 turns into 0 or 00009 turns into 9, which is precisely what I don't want.
Suggestions on what to do, please?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

##### MrExcel MVP
uzbeklana said:
I am trying to sort out some information by separating a column which contains numbers like 1-xxx and xxxx. Most of the numbers that don't have a "1-" in front of it have three zero's and then another number (e.g. 00000, 00009). I want to retain those 0's. I have the column in "text" form so it will do so. BUT, when I do the text to columns with the "-" as the delimited character, the 00000 turns into 0 or 00009 turns into 9, which is precisely what I don't want.
Suggestions on what to do, please?

You could use a formula set like...

B2:

=LEFT(A2,SEARCH("-",A2)-1)

C2:

=REPLACE(A2,1,SEARCH("-",A2),"")

Replies
12
Views
929
Replies
7
Views
223
Replies
1
Views
124
Replies
3
Views
119
Replies
10
Views
1K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,480
Messages
5,770,337
Members
425,612
Latest member
martinijr

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