| If a column of text has a specific value “New Deal” how to I get it to return the cell next to it that contains the dollar amount? I am wanting Excel to quickly sort and enter the data from the left three columns into the middle section to separate everything out. All of the equals signs below are what I need a formula for. It’s not keeping my formatting, but each entity will have its own column and I’m needing excel to sort through this list and put in the corresponding values into the correct columns. So for example I need the left 3 columns searched for “New Deal” if the range contains it I need it to return the amount beside it to the “New Deal” Column
Date Entity Amount New Deal Gale Valley Freeway Dr
1/4/2012 New Deal $(3,500.00) =$(3500)
1/4/2012 Gale Valley $3,583.34 =$3583.34
1/6/2012 Freeway Dr $2,074.50 =$2074.50
1/6/2012 New Deal $276.66 = $276.66
message edited by mel1012003
|
Assuming you have this in B2, the following formula should work:
A B 1 2 New Deal $(3,500.00)Put this in C2 and drag it down:
=IF(LEFT(B2,8)=”New Deal”,RIGHT(B2,LEN(B2)-9)*1,””)
What this formula will do is look at the left 8 characters in B2 and if they “equal” New Deal, it will extract the characters on the right side of cell starting after position 9. That should the position of the first character after the space after New Deal.
Once it has that result, it multiplies it by 1 to turn the text string into a number, which is what I assume you want.
In the example above:
LEFT(B2,8)=”New Deal” —-> TRUE
RIGHT(B2,LEN(B2)-9) —-> $(3,500.00) as a text string
RIGHT(B2,LEN(B2)-9)*1 —-> -3500
Click Here Before Posting Data or VBA Code —> How To Post Data or Code.