Extract string from apha numeric fields
You're welcome!
--
Biff
Microsoft Excel MVP
"RobN" wrote in message
...
Thanks Biff.
Rob
"T. Valko" wrote in message
...
What does the 1E100 do
That's a very very large number, 1 followed by 100 zeros.
It's used to ensure that the number in the string will be extracted. The
way LOOKUP works is if all the numbers in the lookup_vector are smaller
than the lookup_value then the result is the *last* number in the
lookup_vector. Using a gigantic number like 1E100 pretty much guarantees
that all numbers in the lookup_vector will be less than the lookup_value.
--
Biff
Microsoft Excel MVP
"RobN" wrote in message
...
Wow! I'm impressed and I hope the OP is too!
What does the 1E100 do (which changes to IE + 100 when I paste the
formula)?
Rob
"T. Valko" wrote in message
...
A1 = 12z
B1:
=LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))
C1:
=SUBSTITUTE(A1,B1,"")
--
Biff
Microsoft Excel MVP
"RobN" wrote in message
...
Biff,
I think the person asking for a solution wants to have a formula that
will extract both 12 and Z from 12z, etc. Can that be done?
Rob
"T. Valko" wrote in message
...
A1 = 12z
B1 = 12
C1 formula:
=SUBSTITUTE(A1,B1,"")
--
Biff
Microsoft Excel MVP
"mmmbl" wrote in message
...
I have a column (named size) that has contains both numeric and unit
of
measure. I have extracted the numbers into a field but how can i
extract the
unit of measure into another column? Example
ColA Col B Col C
12z = 12 z
12pk= 12 pk
1ct = 1 ct
Thank you!
|