Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull Out Numbers From String
This column is comment. Numbers mixed with text in the cells. They
look like Statue of liberty 12345 statue of liberty 12345 statue of liberty statue of liberty statue of liberty statue of liberty 12345 The number (product order code) could be anywhere. How do I pull out the numbers by formula or by VB code? Thanks in advance. HZ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull Out Numbers From String
Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long If sStr Like "*[0-9]*" Then For i = 1 To Len(sStr) If Mid(sStr, i, 1) Like "[0-9]" Then DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1) End If Next i Else DeleteNonNumerics = sStr End If End Function Or all in place but original data will be overwritten leaving just numerics. Public Sub StripAllAZs() ''strips out everything except numbers Dim myRange As Range Dim cell As Range Dim myStr As String Dim i As Integer With Application .ScreenUpdating = False .Calculation = xlManual End With On Error Resume Next Set myRange = Range(ActiveCell.Address & "," & Selection.Address) _ .SpecialCells(xlCellTypeConstants) If myRange Is Nothing Then Exit Sub If Not myRange Is Nothing Then For Each cell In myRange myStr = cell.text For i = 1 To Len(myStr) If (Asc(UCase(Mid(myStr, i, 1))) < 48) Or _ (Asc(UCase(Mid(myStr, i, 1))) 57) Then myStr = Left(myStr, i - 1) & " " & Mid(myStr, i + 1) End If Next i cell.Value = Application.Trim(myStr) Next cell Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False End If With Application .Calculation = xlAutomatic .ScreenUpdating = True End With End Sub Gord Dibben MS Excel MVP On Thu, 26 Jul 2007 17:47:28 -0700, Curious wrote: This column is comment. Numbers mixed with text in the cells. They look like Statue of liberty 12345 statue of liberty 12345 statue of liberty statue of liberty statue of liberty statue of liberty 12345 The number (product order code) could be anywhere. How do I pull out the numbers by formula or by VB code? Thanks in advance. HZ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull Out Numbers From String
This code works
Function ExtractNumber(target As Range) As Long ExtractNumberString = target Do While (Left(ExtractNumberString, 1) < "0") Or _ (Left(ExtractNumberString, 1) "9") ExtractNumberString = Mid(ExtractNumberString, 2) Loop ExtractNumber = Val(ExtractNumberString) End Function "Curious" wrote: This column is comment. Numbers mixed with text in the cells. They look like Statue of liberty 12345 statue of liberty 12345 statue of liberty statue of liberty statue of liberty statue of liberty 12345 The number (product order code) could be anywhere. How do I pull out the numbers by formula or by VB code? Thanks in advance. HZ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pull Out Numbers From String
You have some good VBA solutions, so here's a formula solution:
With A1 containing text with some consecutive numbers in it. This formula extracts those numbers B1: =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000)))) Note: if there are no numbers, the formula returns 0 Is that something you can work with? *********** Regards, Ron XL2003, WinXP "Curious" wrote: This column is comment. Numbers mixed with text in the cells. They look like Statue of liberty 12345 statue of liberty 12345 statue of liberty statue of liberty statue of liberty statue of liberty 12345 The number (product order code) could be anywhere. How do I pull out the numbers by formula or by VB code? Thanks in advance. HZ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull string before a certain character | Excel Discussion (Misc queries) | |||
Formula to pull first word from text string in a column | Excel Worksheet Functions | |||
pull numbers from text string | Excel Discussion (Misc queries) | |||
Pull last word from a text string in Excel | Excel Worksheet Functions | |||
Pull numbers from a cell | Excel Worksheet Functions |