Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
I've got a column of values like AB2.5 and CG24.3 (this is automatically
generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
ASAP Utilities, a free Add-in has a feature that will strip out either the
Alpha or numeric characters. It's available at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "Heidi" wrote: I've got a column of values like AB2.5 and CG24.3 (this is automatically generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
Heidi, press Alt+F11
and past this VBA code So use this function =digitsonly(a1) regards from Brazil Public Function DigitsOnly(sStr As String) As Variant Dim oRegExp As Object Set oRegExp = CreateObject("VBScript.RegExp") With oRegExp .IgnoreCase = True .Global = True oRegExp.Pattern = "\D" DigitsOnly = oRegExp.Replace(sStr, vbNullString) End With End Function Public Function ASAPFullFileName() As String 'i.e. [c:\test\file.xls] Application.Volatile ASAPFullFileName = ActiveWorkbook.FullName End Function Public Function ASAPFileName() As String 'i.e. [file.xls] Application.Volatile ASAPFileName = ActiveWorkbook.Name End Function Public Function ASAPFilePath() As String 'i.e. [c:\test] Application.Volatile ASAPFilePath = ActiveWorkbook.Path End Function Sub ShowFileInfo(filespec) Dim fs, f, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile(filespec) s = "Criado em: " & f.DateCreated MsgBox s End Sub "Heidi" escreveu: I've got a column of values like AB2.5 and CG24.3 (this is automatically generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
Public Function DigitsOnly(sStr As String) As Variant
Dim oRegExp As Object Set oRegExp = CreateObject("VBScript.RegExp") With oRegExp .IgnoreCase = True .Global = True oRegExp.Pattern = "\D" DigitsOnly = oRegExp.Replace(sStr, vbNullString) End With End Function sorry I past more codes than you will use use just the first part regards "Marcelo" escreveu: Heidi, press Alt+F11 and past this VBA code So use this function =digitsonly(a1) regards from Brazil Public Function DigitsOnly(sStr As String) As Variant Dim oRegExp As Object Set oRegExp = CreateObject("VBScript.RegExp") With oRegExp .IgnoreCase = True .Global = True oRegExp.Pattern = "\D" DigitsOnly = oRegExp.Replace(sStr, vbNullString) End With End Function Public Function ASAPFullFileName() As String 'i.e. [c:\test\file.xls] Application.Volatile ASAPFullFileName = ActiveWorkbook.FullName End Function Public Function ASAPFileName() As String 'i.e. [file.xls] Application.Volatile ASAPFileName = ActiveWorkbook.Name End Function Public Function ASAPFilePath() As String 'i.e. [c:\test] Application.Volatile ASAPFilePath = ActiveWorkbook.Path End Function Sub ShowFileInfo(filespec) Dim fs, f, s Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.GetFile(filespec) s = "Criado em: " & f.DateCreated MsgBox s End Sub "Heidi" escreveu: I've got a column of values like AB2.5 and CG24.3 (this is automatically generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
For a value in A1
If the numbers will always be the final characters in the cell contents, try this: B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),99)*1 (note: that formula returns an error if there are no numbers in the cell) This formula finds numbers anywhere in the cell (returns zero if none): 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: in case text wrap impacts the display, there are no spaces in those formulas. Does that help? *********** Regards, Ron XL2002, WinXP "Heidi" wrote: I've got a column of values like AB2.5 and CG24.3 (this is automatically generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
Wow. Thanks for the speedy reply! I'll try that. I used to have ASAP and
didn't reinstall it on my new machine. Silly me. :) "CLR" wrote: ASAP Utilities, a free Add-in has a feature that will strip out either the Alpha or numeric characters. It's available at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "Heidi" wrote: I've got a column of values like AB2.5 and CG24.3 (this is automatically generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
You're welcome........I've used ASAP Utilities successfully to "clean" data
in imported files for further processing........it's great. Hope it helps for you here......... Vaya con Dios, Chuck, CABGx3 "Heidi" wrote: Wow. Thanks for the speedy reply! I'll try that. I used to have ASAP and didn't reinstall it on my new machine. Silly me. :) "CLR" wrote: ASAP Utilities, a free Add-in has a feature that will strip out either the Alpha or numeric characters. It's available at www.asap-utilities.com Vaya con Dios, Chuck, CABGx3 "Heidi" wrote: I've got a column of values like AB2.5 and CG24.3 (this is automatically generated by a machine and I can't change it). I want to extract just the numeric part (2.5 or 24.3) and have Excel treat it as a number. The letters and numbers are variable in length, so I can't just use LEFT, RIGHT and LEN statements to trim the first/last few characters out. Any ideas? Thank you! Heidi |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract just numeric part of mixed text/number entry?
Hello, What does it mean if you do this and instead of returning the numbers it returns: #VALUE! The code does go in a module, right? thanks -- pdgood ------------------------------------------------------------------------ pdgood's Profile: http://www.excelforum.com/member.php...o&userid=31623 View this thread: http://www.excelforum.com/showthread...hreadid=547556 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I work with part of a cell entry? | Excel Worksheet Functions | |||
Extract Part of String | Excel Worksheet Functions | |||
Summing part of an Alpha Numeric String | Excel Worksheet Functions | |||
Part Number/Qty Consolidations | Excel Discussion (Misc queries) | |||
vlookup to extract part cell content | Excel Discussion (Misc queries) |