Find first instance of a number after a value

[jhayes]

I'm having trouble extracting text from a cell because there are so many variances.

I've tried everything I can think of... to no avail.

I need to display a number in a cell, this number is in another cell but surrounded by other text.

The only thing I think is common among all cells, is that the number is the first number to follow ", " (a comma.. then space) [i.e] , 3 [/]

is there a formula I can use to find the first number that comes after ", " is found?


If you need more explanation please ask I will respond asap.

[VBA Noob]

If it's always a one digit number then try

=MID(A1,FIND(",",A1)+1,1)

otherwise post some more info

VBA Noob

[jhayes]

yes, it is always a 1 digit number, but the problem is there may be multiple instances of the coma+space combination before the number.

(X) ACETIC ACID, GLACIAL, 8,(3),UN2789, PGII Total
(X) ACETIC ACID SOLUTION, 8,UN2790, PGII Total
(X) CORROSIVE LIQUID, ACIDIC, INORGANIC, N.O.S. (PERCHLORIC ACID, CERRIC AMMONIUM NITRATE), 8, UN3264, PGIII Total
(X) 4-METHYLMORPHOLINE, 3,UN2535, PGII Total

I need to extract the bolded number from each of those.
As you can see, there is differences in each... # of chars before and after the necessary character.


Now that I look at it, even if I could just find the #, and pull that out (there are two of these in each...so I would need to extract the first instance of this)
[two instances: the #, and there is a UN####, ( i dont need the UN#### ) ]

[VBA Noob]

This would get 3 out of 4

=IF(ISERROR(MID(A1,FIND(",UN",A1)-4,1)),MID(A1,FIND(", UN",A1)-1,1),MID(A1,FIND(",UN",A1)-1,1))

If you can change the data then select the col > press Ctrl + H > in find box enter (?), and leave the replace box empty

HTH

VBA Noob

[Ron Coderre]

With
A1: (a typical text entry...like you posted)

Try this

Please Login or Register  to view this content.

Is that something you can work with?

[VBA Noob]

Nice one Ron

VBA Noob

[jhayes]

thank you very much

[jhayes]

Well I just saw RONS post...

VBA NOOB, yours worked for %90 of it... I am just playing with the oddball ones (removing (?)...etc.) to make it work 100%

I will try the other posted as well

[jhayes]

Thank you both very much.

Both worked about the same, due to the inconsistent text strings, it's the best I could hope for, I only have to change about 10-15 and I'm all set.
You both just saved me a whole lot of hassle.

THANKS!