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!

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!

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!

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!

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!

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!

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!

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!

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!

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!

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!

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!