Modifying numbers from one long string into 2 shorter strings

[virtuosok]

Hi,
I have a set of numbers in a column in the following format (3 digits, dash, 5 digits):
002-00001
036-01009
153-99182
Let's assume, this is range A2:A1000. For me, meaningful are only the first 3 digits and the last 3 digits (excluding any leading zeros). How do I:
- split these 2 pieces into 2 separate columns (e.g. B2:B1000 and C2:C:1000) and
- lose any leading zeros?
In other words, the output I seek would look as follows

2	1
36	9
153	182

Thanks

 

[RoryA]

You can use Text To Columns on the data tab for that. In the first step choose Fixed length rather than delimited, then in the second step click after the first three digits then again before the last three (that should give you three columns) then in the last step select the second column and choose Do not import, then Finish.

 

[virtuosok]

You can use Text To Columns on the data tab for that. In the first step choose Fixed length rather than delimited, then in the second step click after the first three digits then again before the last three (that should give you three columns) then in the last step select the second column and choose Do not import, then Finish.

Thanks! This doesn't seem to work for me unfortunately, likely because the source column is not formatted as text (or numbers) but being an extract from other column. How do I need to convert please?

 

[RoryA]

If you're using formulas anyway, you can just use something like:
=LEFT(B2,3)+0
=RIGHT(B2,3)+0

 

[virtuosok]

Perfect, thanks - this WORKS.

 

[Peter_SSs]

Another option would be to select B2:B?? and use Data ribbon tab -> Text to columns -> Fixed width -> Next
Click to insert the column dividers after 3 and before last 3

-> Next -> Select the Destination top cell, click in middle column and choose 'Do not import column'

-> Finish

20 09 14.xlsm
	B	C	D
1
2	002-00001	2	1
3	036-01009	36	9
4	153-99182	153	182
5
TTC

 

[RoryA]

Isn't that what I said in post 2, Peter?

 

[Peter_SSs]

Isn't that what I said in post 2, Peter?

Where is that old red face smilie?