Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Text to Columns command in Excel


  • Please log in to reply
1 reply to this topic

#1 Nana23

Nana23

  • Members
  • 3 posts
  • OFFLINE
  •  
  • Local time:05:44 AM

Posted 31 March 2011 - 06:38 AM

I'm pulling data from another database into excel, I have data coming into one column I want to split into to two columns. The second column of data is dropping the leading zeros can anyone tell me how to retain the leading zeros when using the text to column command?

BC AdBot (Login to Remove)

 


#2 tos226

tos226

    BleepIN--BleepOUT


  • Members
  • 1,569 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:LocalHost
  • Local time:05:44 AM

Posted 31 March 2011 - 03:38 PM

There's a way, but I need to know whether you want the resulting column to be treated as text or number. I suspect text, but can't be sure.

Try this little experiment where I want to split after 123
123456
123056
When you go splitting, select the column after the vertical line (it'll turn black), then click on Text option. You will end up with
123 456
123 056
but the stuff ends up as text, so you can't do math on it without conversion.

An alternative would be to insert a ":" or "x" into the strings, and then split on the separator, then remove the separator. However, if numbers result, as they will, your leading zeros will be dropped unless you apply a custom format.

Edited by tos226, 31 March 2011 - 03:42 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users