Solved Select data in cell

December 16, 2013 at 10:57:02
Specs: Windows 7
I appologize if this is a repeat question - I did not see it appear in the unanswered section.

Below is a sample of a cell from a report:

(40) tablets with 1 Refills

I need to be able to pull the quantity (amount in parenthesis) and put it in one cell and pull the amount of refills and put it in a different cell so I can use those cells in a formula.

Any help would be greatly appreciated!


See More: Select data in cell

Report •

✔ Best Answer
December 17, 2013 at 15:05:35
Since Mike's formula works for the Number of Tablets, try this one for the Number of Refills, where F2 contains Mike's Number of Tablets formula:

=MID(F1,FIND(" ",F1,FIND("h",F1))+1,LEN(F1)-24-LEN(F2))

BTW...if you add *1 at the end of both formulas, they'll return a number, not a text string that looks like a number.

BTW, The Sequel...if the singular Tablet or Refill will ever be used, the formula will need to be modified (expanded) to deal with those situations.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
December 16, 2013 at 14:23:14
Try these:

With your data in cell A1

For Number Tablets:

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

For Number of Refills

=MID(A1,FIND({1,2,3,4,5,6,7,8,9,0},A1,FIND(" ",A1,1)),2)

Refills may not exceed 99

See how that works.

MIKE

http://www.skeptic.com/


Report •

#2
December 17, 2013 at 08:17:52
Will give that a try and let you know how it works. Thanks so much for your help!

Report •

#3
December 17, 2013 at 11:31:03
The Number Tablets formula worked like a charm! You would have thought I had won a million dollars!

However, the Number of Refills formula returns a #value in the cell. I will cut and paste exactly what I entered - you may see something I have missed that I don't.

=MID(F1,FIND({1,2,3,4,5,6,7,8,9,0},F1,FIND(" ",F1,1)),2)

My data is in cell F1 and not A1.

Thank you so much!!! You don't know what a lifesaver you are!

Loretta Scott


Report •

Related Solutions

#4
December 17, 2013 at 13:08:02
OK, I see what you mean. Will have to play with it bit more.

MIKE

http://www.skeptic.com/


Report •

#5
December 17, 2013 at 15:05:35
✔ Best Answer
Since Mike's formula works for the Number of Tablets, try this one for the Number of Refills, where F2 contains Mike's Number of Tablets formula:

=MID(F1,FIND(" ",F1,FIND("h",F1))+1,LEN(F1)-24-LEN(F2))

BTW...if you add *1 at the end of both formulas, they'll return a number, not a text string that looks like a number.

BTW, The Sequel...if the singular Tablet or Refill will ever be used, the formula will need to be modified (expanded) to deal with those situations.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Ask Question