Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 10th 04, 09:08 PM
Mira
 
Posts: n/a
Default Display numbers only, no text formula

Is there a formula that will pull the numbers only from
the letters and numbers mixed in?
Example: D16M644HGUD


Thank you!



  #2   Report Post  
Old November 10th 04, 09:50 PM
Frank Kabel
 
Posts: n/a
Default

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=SUM((--MID(0&A1,LARGE(IF(ISNUMBER(-MID(0&A1,seq,1)),seq,1),seq),1))*10
^(seq-1))

where seq is a defined name with the formula
seq: =ROW(INDIRECT("1:255"))

Not sure about the originator of this formula. My guess would be Harlan
or Aladin?

--
Regards
Frank Kabel
Frankfurt, Germany

"Mira" schrieb im Newsbeitrag
...
Is there a formula that will pull the numbers only from
the letters and numbers mixed in?
Example: D16M644HGUD


Thank you!



  #3   Report Post  
Old November 11th 04, 01:54 AM
Gord Dibben
 
Posts: n/a
Default

Mira

Can you use VBA?

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9,.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben Excel MVP

On Wed, 10 Nov 2004 13:08:20 -0800, "Mira"
wrote:

Is there a formula that will pull the numbers only from
the letters and numbers mixed in?
Example: D16M644HGUD


Thank you!




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
Display answer only in another cell of one containing a formula Mally Excel Discussion (Misc queries) 5 January 21st 05 01:07 PM
Convert text to numbers gennario Excel Discussion (Misc queries) 6 January 10th 05 11:56 PM
Sorting when some numbers have a text suffix confused on the tundra Excel Discussion (Misc queries) 5 December 18th 04 10:19 PM
I enter numbers and they are stored as text burkeville Excel Discussion (Misc queries) 5 December 3rd 04 01:59 AM
How to Replace numbers and text with numbers only? Robert Judge Excel Worksheet Functions 3 November 5th 04 04:36 PM


All times are GMT +1. The time now is 06:57 PM.

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