Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #31   Report Post  
Old July 24th 07, 05:38 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2007
Posts: 8
Default A Different approach that may work

Ron - Don't know if you are still monitoring this or will receive an email
notification of this post but I wanted to thank you for this excellent tool.
It's saved me a lot of time and trouble and its much appreciated. That's
besides the fact that its going to make me look good too!

Thanks,
Bill

"Ron Coderre" wrote:

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!


  #32   Report Post  
Old July 24th 07, 05:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 698
Default A Different approach that may work

That post is a year and a half old, but feedback is always welcome. Thanks
for taking the time to let me know that formula helped you out., Bill. Much
appreciated.


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

XL2003, WinXP


"BurghRocks" wrote:

Ron - Don't know if you are still monitoring this or will receive an email
notification of this post but I wanted to thank you for this excellent tool.
It's saved me a lot of time and trouble and its much appreciated. That's
besides the fact that its going to make me look good too!

Thanks,
Bill

"Ron Coderre" wrote:

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!

  #33   Report Post  
Old January 8th 08, 07:20 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Extract Numerics only

This is the only one that works for me, but I lose the last number. My values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.


"Domenic" wrote:

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!


  #34   Report Post  
Old January 8th 08, 09:34 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default Extract Numerics only

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
This is the only one that works for me, but I lose the last number. My
values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.


"Domenic" wrote:

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!




  #35   Report Post  
Old January 9th 08, 01:29 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Extract Numerics only

I need to pull out just the numbers. So ...

1+0756 10756
1+0789AH 10789.

The formula from Domenic gives me results, but chops off the last number. So
....

1+0478BK 1+047
1+0756 1+075
1+0789AH 1+078

I don't have enough knowledge to figure out what's going wrong.

"T. Valko" wrote:

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
This is the only one that works for me, but I lose the last number. My
values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.


"Domenic" wrote:

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!







  #36   Report Post  
Old January 9th 08, 02:15 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Extract Numerics only

On Tue, 8 Jan 2008 11:20:02 -0800, katdot
wrote:

This is the only one that works for me, but I lose the last number. My values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.


This UDF will return all the numbers in the string. If there are NO numbers,
it returns #VALUE! That can be modified, if you wish.

================================
Option Explicit
Function Nums(str As String) As Double
Dim re As Object, mc As Object
Const sPat As String = "\D"
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
Nums = re.Replace(str, "")
End Function
===================================

To enter this <alt-F11 opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code above
into the window that opens.

Then use the formula =Nums(cell_ref) on your worksheet.
--ron
  #37   Report Post  
Old January 9th 08, 02:32 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 2,118
Default Extract Numerics only


Solution from Harlan Grove
A1: abc123def456ghi789

First, create a Named Formula
Names in Workbook: Seq
Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

This ARRAY FORMULA
(committed with CTRL+SHIFT+ENTER, instead of just ENTER)
removes ALL non-numerics from a string.

In sections, for readability:
B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*
10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

In the example, the formula returns: 123456789

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"katdot" wrote in message
...
I need to pull out just the numbers. So ...

1+0756 10756
1+0789AH 10789.

The formula from Domenic gives me results, but chops off the last number.
So
...

1+0478BK 1+047
1+0756 1+075
1+0789AH 1+078

I don't have enough knowledge to figure out what's going wrong.

"T. Valko" wrote:

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
This is the only one that works for me, but I lose the last number. My
values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.


"Domenic" wrote:

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!








  #38   Report Post  
Old January 9th 08, 08:54 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Extract Numerics only

That worked beautifully. It even copied into the attachment to send the boss.

Thank you so much! I just hope my co-workers don't think this means I have
all the answers.

  #39   Report Post  
Old January 9th 08, 09:00 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2008
Posts: 4
Default Extract Numerics only

The Ron's are on a roll. This worked too. I might go with this, since I've
very little knowledge of macros in excel. I'm more comfortable with formulas
and functions, but it's nice to know I have more than one option.

You guys are great! Thanks!

"Ron Coderre" wrote:


Solution from Harlan Grove
A1: abc123def456ghi789

First, create a Named Formula
Names in Workbook: Seq
Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

This ARRAY FORMULA
(committed with CTRL+SHIFT+ENTER, instead of just ENTER)
removes ALL non-numerics from a string.

In sections, for readability:
B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*
10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

In the example, the formula returns: 123456789

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"katdot" wrote in message
...
I need to pull out just the numbers. So ...

1+0756 10756
1+0789AH 10789.

The formula from Domenic gives me results, but chops off the last number.
So
...

1+0478BK 1+047
1+0756 1+075
1+0789AH 1+078

I don't have enough knowledge to figure out what's going wrong.

"T. Valko" wrote:

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
This is the only one that works for me, but I lose the last number. My
values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.


"Domenic" wrote:

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!









  #40   Report Post  
Old January 9th 08, 10:16 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default Extract Numerics only

On Wed, 9 Jan 2008 12:54:04 -0800, katdot
wrote:

That worked beautifully. It even copied into the attachment to send the boss.

Thank you so much! I just hope my co-workers don't think this means I have
all the answers.


Glad to help. Thanks for the feedback.
--ron


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract sub string sixbeforedawn Excel Worksheet Functions 2 October 24th 05 09:50 AM
VBA code to extract m-coefficient in linear trendlines from ALL charts willinusf Excel Discussion (Misc queries) 3 July 12th 05 09:54 PM
Trendline Extract Phil Hageman Charts and Charting in Excel 5 July 6th 05 02:27 AM
Extract Unique Values, Then Extract Again to Remove Suffixes Karl Burrows Excel Discussion (Misc queries) 23 June 25th 05 10:37 PM
Extract specific data into its own workbook via macro? Adrian B Excel Discussion (Misc queries) 2 February 24th 05 06:09 AM


All times are GMT +1. The time now is 05:52 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017