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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I've requested that this question be deleted for the following reason:
Not enough information to confirm an answer.
Not enough information to confirm an answer.
=MID(A1, FIND("$", A1), FIND(" ",A1,FIND("$",A1))-FIND("$
Wayne