| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract string from apha numeric fields
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
How did you get the numbers?
-- Don Guillett Microsoft MVP Excel SalesAid Software "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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
In answer to Don's did I get or extract the numbers
I used this formula that I found in here =LOOKUP(9.99999999999999E+307,--MID(C16,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C16&"0123 456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) RobN was right when he said I need to extract the "z" etc into another column. And this got me an #N/A =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255")))) Thank you! "RobN" wrote: 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! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
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! |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
The formulas did work, I shifted some of the columns and I forgot to change
the second cell reference to match my cells. Thank you for all the help "mmmbl" wrote: In answer to Don's did I get or extract the numbers I used this formula that I found in here =LOOKUP(9.99999999999999E+307,--MID(C16,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C16&"0123 456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) RobN was right when he said I need to extract the "z" etc into another column. And this got me an #N/A =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255")))) Thank you! "RobN" wrote: 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! |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
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! |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Extract string from apha numeric fields
Did you put that formula in B1 or C1 as it should go in B1. I only get an
#NA if there is nothing in the cell A1. Rob "mmmbl" wrote in message ... In answer to Don's did I get or extract the numbers I used this formula that I found in here =LOOKUP(9.99999999999999E+307,--MID(C16,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C16&"0123 456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})) RobN was right when he said I need to extract the "z" etc into another column. And this got me an #N/A =LOOKUP(1E100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255")))) Thank you! "RobN" wrote: 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! |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract numeric part of alphanumeric cell | Excel Worksheet Functions | |||
| Concantenate numeric fields | Excel Worksheet Functions | |||
| How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
| only extract numeric value from alpha numeric cell | Excel Discussion (Misc queries) | |||
| Extract Numbers from Alpha-Numeric String | Excel Worksheet Functions | |||
Linear Mode
