Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Is there a formula that will pull the numbers only from
the letters and numbers mixed in? Example: D16M644HGUD Thank you! |
#2
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
Convert text to numbers | Excel Discussion (Misc queries) | |||
Sorting when some numbers have a text suffix | Excel Discussion (Misc queries) | |||
I enter numbers and they are stored as text | Excel Discussion (Misc queries) | |||
How to Replace numbers and text with numbers only? | Excel Worksheet Functions |