Excel 2010 query

[Louizalass]

In column A there are reference numbers which all have 7 digits a slash then another number after that, eg: 1001234/1

I want to be able to copy/past those numbers from Column A into the next column (B) but the info in that column should show only the first seven digits of the numbers in Column A - ie without the slash at the end.

I know there's a way of doing it, but I don't know what it is.

Can any of you clever people help?

Thanks in advance!
<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=80><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=80></TD></TR></TBODY></TABLE>

 

[Peter_SSs]

Use 'Text to Columns'. It's on the Data ribbon tab. You can use delimited with the / as the delimiter or fixed width with 7 characters. In either case choose not to import the second column. Post back if you need more help with that.

 

[scottylad2]

Orwith your text in Al formula in B1 =Left(A1,7)+0 you only need the plus 0 part if your using the numbers an a maths query, if its for display purposes only then leave the +0 out

If your going to delete the contenrs of col A after you have got your new values, highlight everything in col B, paste special, and check values only

 

[Louizalass]

I followed the instruction to change text to columns and it worked a treat (thank you!) BUT now I have a new problem!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Some of the 7 digit numbers start with zeroes, for instance 0001234<o></o>
<o> </o>
Having separated those first 7 digits from the slash number, reference numbers beginning with zeroes like the one above, actually become less than 7 digit reference numbers because they LOSE their zeros ie 0001234 became 1234<o></o>
<o> </o>
These reference numbers are being copied over to another database and MUST retain the zeros in the reference number. <o></o>
<o></o>
A big home-made, virtual chocolate cake for you if you can figure out how I can keep the 0 digits in the reference numbers! <o></o>

 

[scottylad2]

on your new data, highlight the column, go into the format menu, and custom format to 0000000 click ok

alternative, use the function i posted and it leaves your numbers as is. you could also "join" a leading 0 using the =0&A1 assuming your datas in A1. Any of the latter 2 will rende your numbers as text, if you need it for arithmetic later then you will need to unjoin/manipulate it

 

[Peter_SSs]

I followed the instruction to change text to columns and it worked a treat (thank you!) BUT now I have a new problem!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Some of the 7 digit numbers start with zeroes, for instance 0001234<o></o>
<o> </o>
Having separated those first 7 digits from the slash number, reference numbers beginning with zeroes like the one above, actually become less than 7 digit reference numbers because they LOSE their zeros ie 0001234 became 1234<o></o>
<o> </o>
These reference numbers are being copied over to another database and MUST retain the zeros in the reference number. <o></o>
<o></o>
A big home-made, virtual chocolate cake for you if you can figure out how I can keep the 0 digits in the reference numbers! <o></o>

In the Text to Columns wizard when you get to step 3 of the wizard, the first column down the bottom should be black. Select 'Text' as the 'Column data format' up above that, then select the second column down the bottom and choose 'Do not import column (skip)' in the top section.

 

[Louizalass]

Och! Scotlad - you are genius! It worked a treat!

Thank you so much!

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>

<!-- / icon and title --><!-- message -->

 

[Peter_SSs]

Och! Scotlad - you are genius! It worked a treat!

Hopefully that wasn't the formatting option since that doesn't actually change the underlying value in the cell.

 

[Louizalass]

Uh! Oh! I then discovered I had to save the file in CSV format and when you do that the column loses the zeros again!

Back to the drawing board!

 

[scottylad2]

Uh! Oh! I then discovered I had to save the file in CSV format and when you do that the column loses the zeros again!

Back to the drawing board!

have you tried the =Left(A1,7) option?