CODEWITHSUNDEEP
excelexcel-formula
Peter
Peter

Reputation: 1

Moving only numbers from one column to adjacent column in excel

I have an excel spreadsheet with text and $ amounts in column A, but in different rows. I would like to move only the $ amounts to the adjacent column B. For example, the column looks like this.

The Pool Apache
Frontis

$2,000
1

White River
xiv

$3,000
1

So I would want to move only the $ amounts to the adjacent column.

I'm pretty sure this could be done with a formula, and have read a number of similar posts, but can't quite work out the correct formula.

Upvotes: 0

Views: 10508

Answers (2)

BruceWayne
BruceWayne

Reputation: 23283

Until we can determine a way to detect which cells are currency or not with a formula, you can use you can use this Function, if a VBA solution is okay. (Note: Place in a Workbook Module)

Function is_Currency(ByVal target As Range)
If VarType(target) = vbCurrency Then
    is_Currency = target.Value
Else
    is_Currency = ""
End If
End Function

Edit: Per @ForwardEd, you could also create this isCurrency() UDF, and use it like isNumeric():

Public Function isCurrency(ByVal Target as Range) as Boolean
If varType(Target) = vbCurrency Then isCurrency = TRUE
End Function

Then just put this in your column B: =If(isCurrency(A1),A1,"").

Upvotes: 2

Forward Ed
Forward Ed

Reputation: 9874

=if(isnumber(A1),A1,"")

That will pull only numbers to the adjacent columns which means the $2000 and the 1 would come over. So assuming the pattern is blank cell, money amount, integer blank cell we can modify the formula to:

=if(AND(isnumber(A2),A1=""),A2,"")

Now that formula has to go in cell B2 and gets copied down. Its limitation is that the first A1 can't be the money.

Now if the money is stored as a string life might get a little easier.

=IF(left(A1,1)="$",A1,"")

That formula can go in B1 and get copied down. That will pull all strings starting with $ over to the adjacent column. And the icing on the cake is if you need to turn that string into a number we wold toss in a few more functions to the previous formula and wind up with:

=IF(left(A1,1)="$",SUBSTITUTE(SUBSTITUTE(A1,"$",""),",",""),"")

UPDATE

After coming back from lunch and seeing the new info, I agree with Bruces statement and will give two options. The first is based on length of the value in A1, and the second will be on the breakpoint of 20.

=if(AND(isnumber(A1),len(A1)>2),A1,"")

OR

=if(AND(isnumber(A1),A1>20),A1,"")

OR

=if(AND(isnumber(A1),A1>=100),A1,"")
'I prefer this one as it is a direct check on your criteria for money being no less than $100

All those fomulas can be placed in B1 and copied down.

Upvotes: 2

Related Questions