Need help ASAP! Thanks

**artromanov** · 01-02-2007, 07:08 PM

I need to write a formula which will take only dollar amounts from one column that contains different values including dates and put the dollar amounts into a different column say columb B. How would I do that? Macro or an IF statement... no clue --> please help anyone! THANK YOU! HAPPY NEW YEAR!

**VBA Noob** · 01-02-2007, 07:25 PM

Hi,

Try this.

Say your column is H. With a helper col enter this in J1 and drag down. Note what the formula returns for the dollar values. It returned ,2 for me

=CELL("format",H1)

Then Enter this formula. Change ,2 if required

=SUMIF(I:I,",2",H:H)

VBA Noob

**MDubbelboer** · 01-02-2007, 07:27 PM

Originally Posted by artromanov

I need to write a formula which will take only dollar amounts from one column that contains different values including dates and put the dollar amounts into a different column say columb B. How would I do that? Macro or an IF statement... no clue --> please help anyone! THANK YOU! HAPPY NEW YEAR!

you're probably going to need to provide a bit more info for an answer.

are the dollar values always in the form $##.##
for example is there always two cent characters? is there ever going to be two occurences of the $ sign?
maybe post a couple cells as examples

**MDubbelboer** · 01-02-2007, 07:36 PM

vba noob reads that a different way than me

i read it as one cell has both dates and dollar values, he reads it as alternative

either way, that's an interesting format question

here's a list of the format's available from the help:
If the Microsoft Excel format is CELL returns
General "G"
0 "F0"
#,##0 ",0"
0.00 "F2"
#,##0.00 ",2"
$#,##0_);($#,##0) "C0"
$#,##0_);[Red]($#,##0) "C0-"
$#,##0.00_);($#,##0.00) "C2"
$#,##0.00_);[Red]($#,##0.00) "C2-"
0% "P0"
0.00% "P2"
0.00E+00 "S2"
# ?/? or # ??/?? "G"
m/d/yy or m/d/yy h:mm or mm/dd/yy "D4"
d-mmm-yy or dd-mmm-yy "D1"
d-mmm or dd-mmm "D2"
mmm-yy "D3"
mm/dd "D5"
h:mm AM/PM "D7"
h:mm:ss AM/PM "D6"
h:mm "D9"
h:mm:ss "D8"

it seems to be you need not D*, not G*, assuming percentages do not exist

**VBA Noob** · 01-02-2007, 07:40 PM

Sorry,

I miss read it. The cell format method should still work in a if statement

VBA Noob

**artromanov** · 01-02-2007, 07:50 PM

Thanks guys, i am just still not sure where to put down the formulas.

VBA NOOB gave me two. Yes, there are $xx.xx format one column has different info. I need to extract only the dollar amounts, not dates names or etc. Thats all.