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!

✔ 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

*1at 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

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

Will give that a try and let you know how it works. Thanks so much for your help!

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

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

*1at 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

Ask Your Question

Weekly Poll

Did you buy anything on Amazon's Prime Day?

Discuss in The Lounge

Poll History