numbers only

[silverxx12]

Hi,

i have a a cell with the following format:

""12345678""

Now i want to get rid of the "" and be left with only the number

if i do find and replace, any number beginning with 0 - the first zero is dropped, which i dont want ie i want to see 0023456 not 23456

Any help much appreciated

Silver

 

[JD_GFI]

If all your numbers are of the same length then you can create a custom format to keep the preceding zeroes. Enter a format where you enter zeroes equalling the length of the number:

eg if number length is always 8 digits then enter 00000000 as the custom format.

You can then use find and replace to strip out the quotes without losing the zeroes.

 

[Gerald Higgins]

If they are all 8 digit numbers, try something like this

=mid(a1,3,8)

 

[Gerald Higgins]

JD GFI's solution is probably better, because it converts the values to numbers, mine keeps them as text strings, which you may not want.

 

[CharlesChuckieCharles]

look at SUBSITUTE
<TABLE style="WIDTH: 123pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=164><COLGROUP><COL style="WIDTH: 123pt; mso-width-source: userset; mso-width-alt: 5997" width=164><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 123pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=164>
A30 contains
""009988""
</TD></TR></TBODY></TABLE>=SUBSTITUTE(A30,"""","")
<TABLE style="WIDTH: 77pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=103><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=17 width=103>009988</TD></TR></TBODY></TABLE>