Link to home
Start Free TrialLog in
Avatar of brasiman
brasiman

asked on

Excel formula that looks for character in cell, then copies word to another cell

I have a spreadsheet with 1,000's of rows. In column A, there are comments. There is a ton of text I am trying to sort through. Within this text, I have $ amounts. So in the fields, i have comments and will specify a dollar amount. Almost every row in Column A has a $. The format i have is $1.00 or $1,000.00. Is there a way i can look at Column A for all text that has a $, then copy that whole value to the cell next to it, column B? So if in A1 I have a whole bunch of comments, in the middle is $25.00, i would want $25.00 in B1. All the way down. And a weird question, I am hoping it can look for the $ in the middle of a word too. Every once and a while, as we specify how many units they want to purchase, we put 5$25.00. Which means 5 units for $25 each. So i would want the cell next to it (column B) to copy over the whole text, 5$25.00. I hope that makes sense. Is there a way to do this?
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Assuming your comment is in cell A12, use this formula...

    =MID(A1, FIND("$", A1), FIND(" ",A1,FIND("$",A1))-FIND("$", A1))

Wayne
SOLUTION
Avatar of Wayne Taylor (webtubbs)
Wayne Taylor (webtubbs)
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of brasiman
brasiman

ASKER

Hi Wayne, thank you! I plugged this formula in and it does exactly what I put in my example. As i put it in, it pull data for 75,000 rows in seconds. In doing so, i realized if the qty in front of the $ is more than 9, it only takes the character next to the $. So 15$25.00 comes out as 5$25.00. Is there a way to tweak the formula to say, take the whole thing, before and after the $, using the spaces and the breaking point? So if in the comments, A1, the comments say "The customer wants 125$15.25 and ship ASAP", that the formula looks for the $, then the space before and after, then pulls the full thing..."125$15.25"? Because it could be 1 or up I see up to 10 characters before the $. In my example here, if we change the -1 to -10, it would pull in " wants 125$15.25". Thats why i'm wondering if it can look for spaces. I hope that makes sense.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.