Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macroto delete extra space in a cell
I download a file that contains 5,000 rows of data, each cell shows a numeric value, however, you can not use a formula such as @sum to add the data as each cell apparently contains an extra space after the last digit in the cell. When I select a cell and press F2 I noticed the cursor is 2 spaces behind the last digit, when I hit backspace and deleted the extra space, the value then changed to a number which I can use in a formula. I guess I need to write a macro to the following. Go to each cell and edit and delete the extra space and then proceed to the next cell and perform the same edit. This needs to be done for all records. Any help on how to approach this macro would be helpful
Metfan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macroto delete extra space in a cell
On Wed, 16 Jan 2013 15:24:35 +0000, Metfan wrote:
I download a file that contains 5,000 rows of data, each cell shows a numeric value, however, you can not use a formula such as @sum to add the data as each cell apparently contains an extra space after the last digit in the cell. When I select a cell and press F2 I noticed the cursor is 2 spaces behind the last digit, when I hit backspace and deleted the extra space, the value then changed to a number which I can use in a formula. I guess I need to write a macro to the following. Go to each cell and edit and delete the extra space and then proceed to the next cell and perform the same edit. This needs to be done for all records. Any help on how to approach this macro would be helpful Metfan You may not require all of the tests for valid stuff to convert, but it's there as you did not give a detailed description of your data. The following macro will convert only those cells that contain something (so as to avoid converting blank cells to zero's) do not contain a letter (so as to avoid converting alpha cells) if necessary, you can add other characters to test for to the "letter" test, such as punctuation. As written, this functions only on column A. That is easily changed depending on your worksheet setup. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. =============================== Option Explicit Sub ConvertToNums() Dim r As Range Dim v As Variant Dim i As Long Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp)) v = r For i = LBound(v, 1) To UBound(v, 1) If Not v(i, 1) Like "*[A-Za-z]*" Then If Len(v(i, 1)) 0 Then v(i, 1) = Val(v(i, 1)) End If Next i r = v End Sub =========================== |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macroto delete extra space in a cell
hi Metfan,
copy a blank cell, select all the cells to change, then do a PasteSpecial: xlAdd isabelle Le 2013-01-16 10:24, Metfan a écrit : I download a file that contains 5,000 rows of data, each cell shows a numeric value, however, you can not use a formula such as @sum to add the data as each cell apparently contains an extra space after the last digit in the cell. When I select a cell and press F2 I noticed the cursor is 2 spaces behind the last digit, when I hit backspace and deleted the extra space, the value then changed to a number which I can use in a formula. I guess I need to write a macro to the following. Go to each cell and edit and delete the extra space and then proceed to the next cell and perform the same edit. This needs to be done for all records. Any help on how to approach this macro would be helpful Metfan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macroto delete extra space in a cell
On Wednesday, January 16, 2013 10:24:35 AM UTC-5, Metfan wrote:
I download a file that contains 5,000 rows of data, each cell shows a numeric value, however, you can not use a formula such as @sum to add the data as each cell apparently contains an extra space after the last digit in the cell. When I select a cell and press F2 I noticed the cursor is 2 spaces behind the last digit, when I hit backspace and deleted the extra space, the value then changed to a number which I can use in a formula. I guess I need to write a macro to the following. Go to each cell and edit and delete the extra space and then proceed to the next cell and perform the same edit. This needs to be done for all records. Any help on how to approach this macro would be helpful Metfan -- Metfan select on cell on the right of the first cell with data then type =A1*1 (assume A1 is the first cell with data) copy the formula to the reset of cells below. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging spreadsheets/one has extra space in each cell | Excel Discussion (Misc queries) | |||
delete extra lines within same cell | Excel Programming | |||
delete extra space | Excel Worksheet Functions | |||
How do I delete an extra space in every cell in a column? | Excel Discussion (Misc queries) | |||
Getting rid of extra space after cell contents? | Excel Programming |