Text to column

[wildcard100001]

I need to split a text number. Example 70001

when I use text to column it splits it to 7 1. It does not leave the 000's

I've tried various formats such as text, number etc...

Is there a way to do this without removing the zeros?

Thanks,

Wildcard

 

[xlbo]

Use the fixed width option rather than trying to use a delimeter

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

...AND...

Specify TEXT as the Column data format.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[wildcard100001]

Awesome, many thanks guys.

I wound up formatting the column next to it as Custom 0000, then using the text to column function in the fixed width method.

The method you suggested stripped off the 0's... which I need to merge into another column.


thanks again.

Wildcard

 

[AnotherHiggins]

NOTE: Using the Custom Formatting "0000" does not retain the leading zeros. It only causes Excel to display the number with them.

If you follow Skip's advice and, in the Text-To-Column Wizard, pick TEXT for the second column, then Excel will treat the contents as text and it will not strip leading zeros.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.