Can you help with an excel formula?

Factual Questions
1

I’m trying to create a formula that would copy all the numbers from one row into another row.

All I know how to do is mirror cells, but I can’t use that because the first row has other data in it besides numbers. I guess I"m looking for a way to just pick out the numbers
from Row A and place each number into it’s own cell on Row B.

2

I think

=IF(IsNumber(A1),A1,"")

would work.

3

It works, but it leaves gaps in the row. So if a cell from Row 1 is not a number it would create a blank cell in Row 2. I’m trying to figure out how to create Row 2 without any blank cells.

4

Do you know VBA?

5

I can think of a number ways of kludging a solution using filters or some kind of formula with a running counter but all of them are going to be fragile and easily broken. Using a scripting language to filter the data is the most robust way to approach this; unfortunately, only Virtual Basic for Applications (VBA) is embedded within Excel, and although it would be straightforward to do this in VBA, it requires activing macros which is annoying and sometimes breaks compatibility between versions of Excel. This is an operation you could do on a text or CSV file with just a few lines of Python or Perl, but if you don’t know these languages or can’t deal with exporting the data to a CSV file, VBA is the way to go.

Stranger

6

Do they need to be in order? If not once you use the formula and copy and then paste special values to remove the formulas and leave just the numbers you can sort them by value. The blank cells will all be first or last and can be removed.

If you do need them in order add a column with index nubers in order then sort all columns by the numbers you want remove the rows that are blank, then sort all comuins by the index numbers and things will be back in the original order.

7

One example of that (which might be a help to you) is that the newest versions of Excel have a =SORT(x) function that should be able to sort values within a row. My version of Excel does not have that feature, so I don’t know how it works.

8

nm

9

You could maybe try this (assuming data is in row 1), it pulls out the numbers and puts them in numerical order -

=SMALL(1:1,COLUMN())

Or to get rid of the #NUM! as well use this -

=IF(ISERROR(SMALL(1:1,COLUMN())),"",SMALL(1:1,COLUMN()))
eta: put the formula in cell A2 then copy across.

10

This is one instance where your best solution is going to be a short macro.

Just copy/paste this code block into a macro and execute it when needed.

Should work in all versions of Excel since 2000. Probably all versions, but I can’t test that.



Sub CleanData()
    
    Sheets("Sheet1").Columns(1).Copy Destination:=Sheets("Sheet1").Columns(2)
    
    Set Rng = Range("B:B").SpecialCells(xlCellTypeConstants, 2)
    Rng.Rows.Delete Shift:=xlShiftUp
    
End Sub
11

misread my way isn’t what you want.

12

=IF(IsNumber(A1),A1,"") is a good solution for this problem

13

If you don’t want to mess with .xlm and this is only a one time or occasional thing to do, I would:

  1. Use GreysonCarlisle’s method.
  2. Copy that column and paste as values (the clipboard with 1 2 3 on it) in new column or overwrite the one you copied.
  3. Select the value column, hit F5 > Special > Check “Blanks” > OK > Right click any of the highlighted cells > Delete > Shift cells up