A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

data conversion



 
 
Thread Tools Display Modes
  #11  
Old April 1st, 2010, 11:57 PM posted to microsoft.public.excel.misc
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default data conversion

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 Substitutepaste SpecialValues.

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.
--
Regards
Roger Govier

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" 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
columnCopyPaste SpecialValues 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" wrote in message
...
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 columnCopyPaste SpecialValues
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.


  #12  
Old June 4th, 2010, 09:10 PM posted to microsoft.public.excel.misc
BlackBayou
external usenet poster
 
Posts: 2
Default data conversion

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" 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
columnCopyPaste SpecialValues 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" wrote in message
...
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 columnCopyPaste SpecialValues
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.


.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.