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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
data conversion
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. |
#2
|
|||
|
|||
data conversion
Hi Candida
In a blank cell enter 1 Copy that cellmark your block of dataPaste SpecialMultiply -- Regards Roger Govier 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. I have 3000 rows of numbers that came through like that Please help. |
#3
|
|||
|
|||
data conversion
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" 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. I have 3000 rows of numbers that came through like that Please help. |
#4
|
|||
|
|||
data conversion
"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. |
#5
|
|||
|
|||
data conversion
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. |
#6
|
|||
|
|||
data conversion
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. "FSt1" wrote: 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" 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. I have 3000 rows of numbers that came through like that Please help. |
#7
|
|||
|
|||
data conversion
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. |
#8
|
|||
|
|||
data conversion
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. |
#9
|
|||
|
|||
data conversion
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. |
#10
|
|||
|
|||
data conversion
"Candida" wrote:
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. 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 ----- "Candida" wrote: 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. "FSt1" wrote: 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" 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. I have 3000 rows of numbers that came through like that Please help. |
|
Thread Tools | |
Display Modes | |
|
|