Prev: Move rows into columns
Next: Separate AM/PM from time.
From: BlackBayou on 4 Jun 2010 16:10
there are many situations where your suggestion to just leave the apostrophe
in does not work, for example one of many, lookup will not return matches if
reference list does not have apostrophies

"Joe User" wrote:

> Candida wrote:
> > That did work for some except I have numbers like
> > '01.002.50.001 for part numbers and it did not work
> > for those. Any other ideas?
>
>
> "Roger Govier" <roger(a)technology4nospamu.co.uk> wrote:
> > But those values are Text. They cannot be numeric.
>
> I concur. I would go further to suggest that they should remain text.
>
> Modifying and treating them as numbers can create new problems. For
> example, leading zeros go away and long numbers might be displayed
> differently unless you change the cell format. And very long "numbers"
> (more than 15 significant digits) might be changed irrevocably.
>
>
> > the only difference being your numerics end up right
> > justified, whilst the other Text values are right
> > justified
>
> Ah, text is left justified by default.
>
> If that's the problem, change cell Alignment under Format Cells, setting
> Horizontal to Right.
>
>
> > If it is the presence of the single quote that is giving
> > you the problem
>
> ..... I cannot imagine why it would ....
>
>
> > then you could use another column and enter
> > =SUBSTITUE(A1,"'","")
> > and copy down. Then mark the whole of the new
> > column>Copy>Paste Special>Values and your data will
> > not have the leading '
>
> First, it is sufficient to copy the cells, then use Edit or right-click
> Paste Special > Values (in Excel 2003).
>
> Second, I do not believe the SUBSTITUTE really does anything. The leading
> apostrophe is transparent; it is not considered part of the value. For
> example, FIND(A1,"'") returns an error; and we write
> IF(A1="01.002.50.001",...), not IF(A1="'01.002.50.001",...).
>
>
> ----- original message -----
>
> "Roger Govier" <roger(a)technology4nospamu.co.uk> wrote in message
> news:4BB4B83D.2000300(a)technology4nospamu.co.uk...
> > Hi Candida
> >
> > But those values are Text. They cannot be numeric.
> > If you used the suggestion by Joe, Data Text to columns, then that does
> > work, the only difference being your numerics end up right justified,
> > whilst the other Text values are right justified
> >
> > If it is the presence of the single quote that is giving you the problem,
> > but you still want everything to remain left justified, then you could use
> > another column and enter
> > =SUBSTITUE(A1,"'","")
> > and copy down.
> > Then mark the whole of the new column>Copy>Paste Special>Values
> > and your data will not have the leading '
> >
> > --
> > Regards
> > Roger Govier
> >
> > Candida wrote:
> >> That did work for some except I have numbers like '01.002.50.001 for part
> >> numbers and it did not work for those. Any other ideas?
> >>
> >> "Joe User" wrote:
> >>
> >>> "Candida" wrote:
> >>>> Please advise how I can convert a number that
> >>>> comes over as '40593 from our Italian firm. How
> >>>> do I take the ' off of the number.
> >>> In Excel 2003, select the cells, click on Data > Text to Columns. In
> >>> this case, you can breeze through the wizard by clicking Next > Next >
> >>> Finish.
>
> .
>
 | 
Pages: 1
Prev: Move rows into columns
Next: Separate AM/PM from time.