Extracting a number from a variable string

If you have a string followed by a space and then a number you can do something like this:

=RIGHT(A2,LEN(A2)−FIND(" ",A2,1))*1

That won't work when there are following non-number characters as in Clonazepam 500+. If there aren't too cases where you have trailing non-number characters you could strip them manually.

Substitute ; for , in the formula if your region uses , as a decimal separator.

SG

If you have a consistent pattern and in some cases a final + then you could "preprocess" in a separate column like this:'

Formula in B2, filled down:

=IF(RIGHT(A2,1)="+",LEFT(A2,LEN(A2)−1),A2)

Formula in C2, filled down:

=IFERROR(RIGHT(B2,LEN(B2)−FIND(" ",B2,1))*1,0)

Or, if you like you could put it all in one long, ugly formula:

In B2, filled down:

=IFERROR(RIGHT(IF(RIGHT(A2,1)="+",LEFT(A2,LEN(A2)−1),A2),LEN(IF(RIGHT(A2,1)="+", LEFT(A2,LEN(A2)−1),A2))−FIND(" ",IF(RIGHT(A2,1)="+",LEFT(A2,LEN(A2)−1),A2),1))*1,0)

SG

If it is just one specific character (i.e., the "+") or a small set of characters that dangle at the end of the string after the number, You can first run the string through SUBSTITUTE, turning the +'s into null strings.

=SUBSTITUTE(A2,"+","")

or if you have two characters that might be at the end, say they are a "+" or a "-" (or both or multiples of both)

=SUBSTITUTE(SUBSTITUTE(A2,"+",""),"-","")

Then you can strip the text from the front as Barry showed to extract the number by itself.

Honestly, I would simply avoid encoding the thing and the amount in the same text. If it's all mixed together, then I suggest a step of creating two columns then move forward with the two columns.

Not knowing how many items there are and all of the types in the list it's hard to provide specific suggestions.

"Then you can strip the text from the front as Barry showed to extract the number by itself."

Well, I had thought of that, and later, of using SUBSTITUTE, but it was SGIII that actually posted the first part, and you that posted the SUSTITUTE suggestion.

Wayne correctly And I think we all agree with SG's implied non-support of the "one long, ugly formula" single column solution provided.

Regards,

Barry