data conversion

  • Thread starter Thread starter Candida
  • Start date Start date
C

Candida

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. I have 3000 rows of
numbers that came through like that Please help.
 
Hi Candida

In a blank cell enter 1
Copy that cell>mark your block of data>Paste Special>Multiply
 
hi
1 way..
enter a 1(one) in a cell off to the side. copy it. then select the cell with
the apostropheed number. paste special multiply.
that should get rid of the apostrophe

regards
FSt1
 
Candida said:
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.
Click to expand...

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.
 
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?
 
this works for single digits like '45632 however I have some more complex
numbers such as '01.02354.321 or '01.54211 and this doesn't seem to work for
those numbers.
Please help.
 
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 '
 
Candida said:
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?
Click to expand...


Roger Govier said:
But those values are Text. They cannot be numeric.
Click to expand...

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
Click to expand...

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
Click to expand...

..... 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 '
Click to expand...

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 -----
 
Candida said:
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?
Click to expand...


Roger Govier said:
But those values are Text. They cannot be numeric.
Click to expand...

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
Click to expand...

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
Click to expand...

..... 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 '
Click to expand...

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 -----
 
Candida said:
this works for single digits like '45632 however
I have some more complex numbers such as
'01.02354.321 or '01.54211 and this doesn't
seem to work for those numbers.
Click to expand...

But those should be left as text. What problem does that cause for you?

If the problem is that text is left-justified by default, change cell
Alignment under Format Cells, setting Horizontal to Right.

Otherwise, what problem does the apostrophe cause for you?

The leading apostrophe is transparent; it is not considered part of the
value. For example, you would write IF(A1="01.02354.321",...), not
IF(A1="'01.02354.321",...).

The leading apostrophe is just special syntax that tells Excel not to
consider the numeric text as a number, which should be what you want.

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.


----- original message -----
 
Joe
My suggestion for Substitute, does not in any way change the value from
being a Text value.

There is a visual difference of what is displayed in the formula bar,
before and after carrying out the Substitute>paste Special>Values.

I cannot understand why the OP would be concerned about the leading ',
but they obviously were, else they wouldn't have posted back.

I was merely suggesting a way of removing it.
 
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
 
Back
Top