A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Extract Numerics only



 
 
Thread Tools Display Modes
  #21  
Old December 2nd, 2005, 07:54 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

A little more info on my problem:

This formula works:

=IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)=" OFF ROAD","MANUAL
INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7,8,9},N3381&"0123456789" )),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})),2))))

By adding to more conditions to the front of it, I get an error:

=IF(N3381="M8",35,IF(VALUE(RIGHT(C3881,3))=814,33, IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)="O FF
ROAD","MANUAL
INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7,8,9},N3381&"0123456789" )),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})),2))))))




"Corey" wrote:

This works perfect Peo! Thanks a bunch! Also to you Domenic for coming up
with that original formula. I tried Bob's suggestion to this as well, but got
#VALUE!. Since your formula returns the everything to the right, starting
with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the first
two numbers. This is the division number I've been trying to get at. Perhaps
if I said that earlier, there's another way to get it done. ?? Anyways, I've
also added two if statements to the beginning of that and it works great.
When I try to add a third I get an error that leads me to the "SEARCH"
portion of the formula. Is there a limit to how many conditionals I can put
in this thing?

Thanks again!

"Peo Sjoblom" wrote:

The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is 769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this adaptation
of Domenic's excellent formula

=LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA nor

UDF.
Also, I'm trying to put together a tool for a below novice user of Excel

as
it takes her hours to decipher a statement. I was hoping to use just a

simple
formula and have her cut and paste the data to have it automatically

populate
with this formula. I've tried yours Domenic, and it seems to work for the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out

the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!




  #22  
Old December 2nd, 2005, 07:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

No Peo, it still gives 114005, it just makes the result a number as against
the string. The reason I even mentioned it was because in Domenic's
original; formula, the -- was by the MID, and as you showed, that won't
work, by preceding it does.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peo Sjoblom" wrote in message
...
I believe the OP complained that he got 769142 instead of 11-4005 thus the
change, otherwise one might as well keep Domenic's original formula


--

Regards,

Peo Sjoblom

"Bob Phillips" wrote in message
...
You might want to convert that to a number



=--LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4, 5,6,7,8,9},A1&"01234567
89
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peo Sjoblom" wrote in message
...
The reason the first returns 769142 is that the extracted value

11-4005
is seen as a date so Excel translates the date as 11/01/05 which is

769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this

adaptation
of Domenic's excellent formula




=LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA

nor
UDF.
Also, I'm trying to put together a tool for a below novice user of

Excel
as
it takes her hours to decipher a statement. I was hoping to use just

a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work

for
the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull

out
the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!









  #23  
Old December 2nd, 2005, 08:02 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),SUMPRODUCT(LEN
(A1)-LEN(SUBSTITUTE(A1,{"-",0,1,2,3,4,5,6,7,8,9},""))))

....which will return...

11-4005
040652
3127
2679
20
130

....when you have the following...

OFF 11-4005
ON 040652
ON 3127
ON2679
O/F 20R
OFF///130H

Hope this helps!

In article ,
"Corey" wrote:

Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF.
Also, I'm trying to put together a tool for a below novice user of Excel as
it takes her hours to decipher a statement. I was hoping to use just a simple
formula and have her cut and paste the data to have it automatically populate
with this formula. I've tried yours Domenic, and it seems to work for the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!


  #24  
Old December 2nd, 2005, 08:08 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

Corey,

My formula actually returns 11 from the string OFF 11-4005 as it strips off
anything before the first numeric, and anything after the first non-numeric
after that.

Did you insert the name formula, and array enter everything?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Corey" wrote in message
...
This works perfect Peo! Thanks a bunch! Also to you Domenic for coming up
with that original formula. I tried Bob's suggestion to this as well, but

got
#VALUE!. Since your formula returns the everything to the right, starting
with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the

first
two numbers. This is the division number I've been trying to get at.

Perhaps
if I said that earlier, there's another way to get it done. ?? Anyways,

I've
also added two if statements to the beginning of that and it works great.
When I try to add a third I get an error that leads me to the "SEARCH"
portion of the formula. Is there a limit to how many conditionals I can

put
in this thing?

Thanks again!

"Peo Sjoblom" wrote:

The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is

769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this

adaptation
of Domenic's excellent formula


=LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA

nor
UDF.
Also, I'm trying to put together a tool for a below novice user of

Excel
as
it takes her hours to decipher a statement. I was hoping to use just a

simple
formula and have her cut and paste the data to have it automatically

populate
with this formula. I've tried yours Domenic, and it seems to work for

the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull

out
the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!






  #25  
Old December 2nd, 2005, 08:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

I know this is a bit late, but I just came up with this, which seems to work
well:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If finds the first digit location and the count of digits in the string and
uses those values in the MID function.

***********
Regards,
Ron


"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!

  #26  
Old December 2nd, 2005, 08:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

That sounds like too many nested functions. Put some of the tests in a
separate cell, and then include a test on that cell in the main formula.
That is exactly the problem I got with my formula, which is what I use the
name formula for.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Corey" wrote in message
...
A little more info on my problem:

This formula works:

=IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)=" OFF ROAD","MANUAL

INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7
,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,1 1,12,13,14,15})),2))))

By adding to more conditions to the front of it, I get an error:


=IF(N3381="M8",35,IF(VALUE(RIGHT(C3881,3))=814,33, IF(VALUE(RIGHT(C3381,3))=1
07,7,IF(LEFT(N3381,8)="OFF
ROAD","MANUAL

INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7
,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,1 1,12,13,14,15})),2))))))




"Corey" wrote:

This works perfect Peo! Thanks a bunch! Also to you Domenic for coming

up
with that original formula. I tried Bob's suggestion to this as well,

but got
#VALUE!. Since your formula returns the everything to the right,

starting
with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the

first
two numbers. This is the division number I've been trying to get at.

Perhaps
if I said that earlier, there's another way to get it done. ?? Anyways,

I've
also added two if statements to the beginning of that and it works

great.
When I try to add a third I get an error that leads me to the "SEARCH"
portion of the formula. Is there a limit to how many conditionals I can

put
in this thing?

Thanks again!

"Peo Sjoblom" wrote:

The reason the first returns 769142 is that the extracted value

11-4005
is seen as a date so Excel translates the date as 11/01/05 which is

769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this

adaptation
of Domenic's excellent formula


=LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA

nor
UDF.
Also, I'm trying to put together a tool for a below novice user of

Excel
as
it takes her hours to decipher a statement. I was hoping to use just

a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work

for the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull

out
the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!






  #27  
Old December 2nd, 2005, 08:23 PM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

Nope....it misses punctuation within the numbers, as in:
OFF 11-4005

***********
Regards,
Ron


"Ron Coderre" wrote:

I know this is a bit late, but I just came up with this, which seems to work
well:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If finds the first digit location and the count of digits in the string and
uses those values in the MID function.

***********
Regards,
Ron


"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!

  #28  
Old December 3rd, 2005, 02:14 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only

When I use your formula I get 769142 not 114005 also if you use number you
won't get a leading zero as in the OP's second example
--
Regards,

Peo Sjoblom

(No private emails please)


"Bob Phillips" wrote in message
...
No Peo, it still gives 114005, it just makes the result a number as
against
the string. The reason I even mentioned it was because in Domenic's
original; formula, the -- was by the MID, and as you showed, that won't
work, by preceding it does.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peo Sjoblom" wrote in message
...
I believe the OP complained that he got 769142 instead of 11-4005 thus
the
change, otherwise one might as well keep Domenic's original formula


--

Regards,

Peo Sjoblom

"Bob Phillips" wrote in message
...
You might want to convert that to a number



=--LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4, 5,6,7,8,9},A1&"01234567
89
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peo Sjoblom" wrote in message
...
The reason the first returns 769142 is that the extracted value

11-4005
is seen as a date so Excel translates the date as 11/01/05 which is

769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this
adaptation
of Domenic's excellent formula




=LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4 ,5,6,7,8,9},A1&"0123456789
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA

nor
UDF.
Also, I'm trying to put together a tool for a below novice user of

Excel
as
it takes her hours to decipher a statement. I was hoping to use
just

a
simple
formula and have her cut and paste the data to have it
automatically
populate
with this formula. I've tried yours Domenic, and it seems to work

for
the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Hope this helps!

In article ,
"Corey" wrote:

Hello. I have a column with data that contain numerics and
text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull

out
the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!










  #29  
Old December 3rd, 2005, 04:35 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default A Different approach that may work

I've been playing with this formula, which pulls from the first digit found
thru the last digit found:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1+MAX(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIREC T("1:"&LEN(A1))))-MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter].

So...
"OFF 11-4005ABC" would result in 11-4005
"OFF///130H" would result in 130
etc

I think that handles all of the pertinent scenarios...let me know.

***********
Regards,
Ron


"Ron Coderre" wrote:

Nope....it misses punctuation within the numbers, as in:
OFF 11-4005

***********
Regards,
Ron


"Ron Coderre" wrote:

I know this is a bit late, but I just came up with this, which seems to work
well:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If finds the first digit location and the count of digits in the string and
uses those values in the MID function.

***********
Regards,
Ron


"Corey" wrote:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!

  #30  
Old December 3rd, 2005, 08:26 AM posted to microsoft.public.excel.misc
external usenet poster
 
Posts: n/a
Default Extract Numerics only


this may not be what you want - but I would take both or 1 column at a
time and past it into a blank "WORD" document - then go to find and
replace and under the "MORE" tab - Select "SPECIAL" and then delete
Charaters or Digets as required.

Not as elegant as functions and formulas - but alot easier.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2299087

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
a tip about how to extract .SWFfile from PowerPoint file SusanZheng Powerpoint 1 November 3rd, 2005 04:58 AM
Extract records with a specific field appearing more than once in the DB markx Running & Setting Up Queries 7 September 28th, 2005 06:44 PM
Trendline Extract Phil Hageman Charts and Charting 5 July 6th, 2005 02:27 AM
Extract Unique Values, Then Extract Again to Remove Suffixes Karl Burrows General Discussion 23 June 25th, 2005 10:37 PM
Extract specific data into its own workbook via macro? Adrian B General Discussion 2 February 24th, 2005 07:09 AM


All times are GMT +1. The time now is 11:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.