Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to values
In Excel2000, what is the best way to code a macro that will convert a
column of text to values. For example, I have the following in column A: '100 '1000 '20 I want this to be converted to values like this: 100 1000 20 Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to values
Snax,
You could use a formula such as in cell B1 =RIGHT(A1,LEN(A1)-1) where A1 was your '100 value. Just copy the formula down. In essence, it says, take the right most characters, except the very first charcter (length - 1). Hope that helps. Regards, Kevin "snax500" wrote in message om... In Excel2000, what is the best way to code a macro that will convert a column of text to values. For example, I have the following in column A: '100 '1000 '20 I want this to be converted to values like this: 100 1000 20 Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to values
Sorry, disregard my answer...not a macro.
Kevin "Kevin Stecyk" wrote in message ... Snax, You could use a formula such as in cell B1 =RIGHT(A1,LEN(A1)-1) where A1 was your '100 value. Just copy the formula down. In essence, it says, take the right most characters, except the very first charcter (length - 1). Hope that helps. Regards, Kevin "snax500" wrote in message om... In Excel2000, what is the best way to code a macro that will convert a column of text to values. For example, I have the following in column A: '100 '1000 '20 I want this to be converted to values like this: 100 1000 20 Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to values
As a matter of interest:
nor would your formula approach work since the single apostrophe is viewed as a formatting character and would be ignored by the formula. So the formula would remove the first digit on the left as well. =VALUE(A1) would work. Sub Converttonumbers() for each cell in selection.specialcells(xlconstants,xltextvalues) if isnumeric(cell.value) then cell.Formula = cdbl(cell.Value) end if Next End Sub -- Regards, Tom Ogilvy "Kevin Stecyk" wrote in message ... Sorry, disregard my answer...not a macro. Kevin "Kevin Stecyk" wrote in message ... Snax, You could use a formula such as in cell B1 =RIGHT(A1,LEN(A1)-1) where A1 was your '100 value. Just copy the formula down. In essence, it says, take the right most characters, except the very first charcter (length - 1). Hope that helps. Regards, Kevin "snax500" wrote in message om... In Excel2000, what is the best way to code a macro that will convert a column of text to values. For example, I have the following in column A: '100 '1000 '20 I want this to be converted to values like this: 100 1000 20 Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to values
in B1 try this:
=value(a1) if you have blanks: =if(a1="","",value(a1)) this is using a formula instead of code. James -----Original Message----- In Excel2000, what is the best way to code a macro that will convert a column of text to values. For example, I have the following in column A: '100 '1000 '20 I want this to be converted to values like this: 100 1000 20 Thanks . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
text to values
Try:
Sub ConvertText() Columns("A:A").TextToColumns Destination:=Range("A1") End Sub In om, snax500 typed: In Excel2000, what is the best way to code a macro that will convert a column of text to values. For example, I have the following in column A: '100 '1000 '20 I want this to be converted to values like this: 100 1000 20 Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumif using Values, but returning text or values | Excel Discussion (Misc queries) | |||
Plotting XY with X text values? Credit ratings and their ROE values | Charts and Charting in Excel | |||
How do I sum values in a row if the value in a row above = text | Excel Worksheet Functions | |||
Text values to numeric values | Excel Discussion (Misc queries) | |||
Text Values | Excel Discussion (Misc queries) |