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
  #41  
Old January 9th, 2008, 09:54 PM posted to microsoft.public.excel.misc
katdot
external usenet poster
 
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.

  #42  
Old January 9th, 2008, 10:00 PM posted to microsoft.public.excel.misc
katdot
external usenet poster
 
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(-(seqTRANSPOSE(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!









  #43  
Old January 9th, 2008, 11:16 PM posted to microsoft.public.excel.misc
Ron Rosenfeld
external usenet poster
 
Posts: 3,719
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
 




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:12 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.