Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I need to find an easy function or macro to remove all text characters from a
cell. EX. Pail=19KG converts to 19 |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
Please elaborate - if you want to clear all cells containing text use F5, Special, Constant, Text. In a cell containing "asderf" you could use a formula like =IF(ISTEXT(A1),"",A1) If a cell contains qwe345 then technically 345 is text! Give us a few examples. -- If this helps, please click the Yes button Cheers, Shane Devenshire "volleygods" wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I guess "text" is not the correct word. I need to remove the alpha
characters and leave the numeric characters. I get a spread sheet report that has a column with cells in the various formats like drum=245KG and I need to multiply the 245 by another number so i need to remove the drum=KG part to be left with just the number. There are roughly 1000 lines in the sheet so manually doing this is out of the question. Ex. drum=245KG goes to 245 pail=19KG goes to 19 case=12KG goes to 12 "Shane Devenshire" wrote: Hi, Please elaborate - if you want to clear all cells containing text use F5, Special, Constant, Text. In a cell containing "asderf" you could use a formula like =IF(ISTEXT(A1),"",A1) If a cell contains qwe345 then technically 345 is text! Give us a few examples. -- If this helps, please click the Yes button Cheers, Shane Devenshire "volleygods" wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 |
#4
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
If the number you want **ALWAYS** follows an equal sign...
=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("=",A1)+1,99),ROW($1:$99))) -- Rick (MVP - Excel) "volleygods" wrote in message ... I guess "text" is not the correct word. I need to remove the alpha characters and leave the numeric characters. I get a spread sheet report that has a column with cells in the various formats like drum=245KG and I need to multiply the 245 by another number so i need to remove the drum=KG part to be left with just the number. There are roughly 1000 lines in the sheet so manually doing this is out of the question. Ex. drum=245KG goes to 245 pail=19KG goes to 19 case=12KG goes to 12 "Shane Devenshire" wrote: Hi, Please elaborate - if you want to clear all cells containing text use F5, Special, Constant, Text. In a cell containing "asderf" you could use a formula like =IF(ISTEXT(A1),"",A1) If a cell contains qwe345 then technically 345 is text! Give us a few examples. -- If this helps, please click the Yes button Cheers, Shane Devenshire "volleygods" wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 |
#5
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
volleygods wrote:
I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 =LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) Credit to Bob Phillips |
#6
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
A tad shorter and it eliminates the volatile INDIRECT function call...
=LOOKUP(9.99999999999999E+307,--LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012 3456789")),99),ROW($1:$99))) -- Rick (MVP - Excel) "Glenn" wrote in message ... volleygods wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 =LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) Credit to Bob Phillips |
#7
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Function RemAlpha(str As String) As String
'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" RemAlpha = re.Replace(str, "") End Function =RemAlpha(cellref)*1 the *1 produces a numeric value Gord Dibben MS Excel MVP On Tue, 23 Dec 2008 10:55:07 -0800, volleygods wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 |
#8
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Tue, 23 Dec 2008 12:07:01 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
Function RemAlpha(str As String) As String 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" RemAlpha = re.Replace(str, "") End Function =RemAlpha(cellref)*1 the *1 produces a numeric value Gord Dibben MS Excel MVP A few comments: Your formula: RemAlpha(cell_ref)*1 will return a #VALUE! error if there were no digits in cell_ref. So if you wanted to return a numeric value, with a #VALUE! error if there are no digits, you could modify your UDF: Option Explicit Function RemAlpha(str As String) As Variant 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" RemAlpha = CDbl(re.Replace(str, "")) End Function and then use the simpler formula =RemAlpha(cell_ref). --------------------------------- Also, your routine will remove decimals. In other words, pail=19.3kg would return 193 and not 19.3. If decimal values are a possibility, there are several other approaches. If the only "dot" could be in the number, then you could change pattern to "[^\d.]" Of course, this would fail with "pail=19.3kg." So what you could use is a regex that would extract a floating point number. Perhaps: Dim re as object, mc as object Set re = createobject("vbscript.regexp") re.Pattern = "\d*\.?\d+" If re.test(str) = True then Set mc = re.Execute(str) end if RemAlpha = mc(0).Value --ron |
#9
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Wow!!
So many things to consider other than the sample OP posted. Thanks Ron On Tue, 23 Dec 2008 16:12:18 -0500, Ron Rosenfeld wrote: On Tue, 23 Dec 2008 12:07:01 -0800, Gord Dibben <gorddibbATshawDOTca wrote: Function RemAlpha(str As String) As String 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" RemAlpha = re.Replace(str, "") End Function =RemAlpha(cellref)*1 the *1 produces a numeric value Gord Dibben MS Excel MVP A few comments: Your formula: RemAlpha(cell_ref)*1 will return a #VALUE! error if there were no digits in cell_ref. So if you wanted to return a numeric value, with a #VALUE! error if there are no digits, you could modify your UDF: Option Explicit Function RemAlpha(str As String) As Variant 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" RemAlpha = CDbl(re.Replace(str, "")) End Function and then use the simpler formula =RemAlpha(cell_ref). --------------------------------- Also, your routine will remove decimals. In other words, pail=19.3kg would return 193 and not 19.3. If decimal values are a possibility, there are several other approaches. If the only "dot" could be in the number, then you could change pattern to "[^\d.]" Of course, this would fail with "pail=19.3kg." So what you could use is a regex that would extract a floating point number. Perhaps: Dim re as object, mc as object Set re = createobject("vbscript.regexp") re.Pattern = "\d*\.?\d+" If re.test(str) = True then Set mc = re.Execute(str) end if RemAlpha = mc(0).Value --ron |
#10
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
On Tue, 23 Dec 2008 14:10:35 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
So many things to consider other than the sample OP posted. That's true in many instances. But with regular expressions I find it much quicker to make those kinds of adjustments. (It was NOT that way when I started using them, and I'm still a novice compared to many, but I'm learning). --ron |
#11
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
If the OP is looking for a UDF, here is a non-Regular Expressions on he can
consider also... Function RemoveAlpha(Rng As Range) As Variant Dim X As Long If Not Rng.Value Like "*#*" Then RemoveAlpha = CVErr(xlErrValue) Else For X = 1 To Len(Rng.Value) RemoveAlpha = Val(Mid(Rng.Value, X)) If RemoveAlpha < 0 Then Exit For Next End If End Function -- Rick (MVP - Excel) "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Function RemAlpha(str As String) As String 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True re.Pattern = "\D" RemAlpha = re.Replace(str, "") End Function =RemAlpha(cellref)*1 the *1 produces a numeric value Gord Dibben MS Excel MVP On Tue, 23 Dec 2008 10:55:07 -0800, volleygods wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG converts to 19 |
#12
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Incorrect Gord
see Ron's answer... On 23 Gru, 21:07, Gord Dibben <gorddibbATshawDOTca wrote: Function RemAlpha(str As String) As String 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") * * re.Global = True * * re.Pattern = "\D" RemAlpha = re.Replace(str, "") End Function =RemAlpha(cellref)*1 * * *the *1 produces a numeric value Gord Dibben *MS Excel MVP On Tue, 23 Dec 2008 10:55:07 -0800, volleygods wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG *converts to 19- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
#13
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Already noted Jarek
Thanks for the pointing-out. Gord On Tue, 23 Dec 2008 23:39:01 -0800 (PST), Jarek Kujawa wrote: Incorrect Gord see Ron's answer... On 23 Gru, 21:07, Gord Dibben <gorddibbATshawDOTca wrote: Function RemAlpha(str As String) As String 'Remove Alphas from a string Dim re As Object Set re = CreateObject("vbscript.regexp") * * re.Global = True * * re.Pattern = "\D" RemAlpha = re.Replace(str, "") End Function =RemAlpha(cellref)*1 * * *the *1 produces a numeric value Gord Dibben *MS Excel MVP On Tue, 23 Dec 2008 10:55:07 -0800, volleygods wrote: I need to find an easy function or macro to remove all text characters from a cell. EX. Pail=19KG *converts to 19- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Removing extra characters in a cell | Excel Discussion (Misc queries) | |||
Removing non text characters from spreadsheet | Excel Discussion (Misc queries) | |||
Removing characters from a cell (keeping only the numbers) | Excel Discussion (Misc queries) | |||
Removing text characters | Excel Worksheet Functions | |||
removing some of the characters from a cell | Excel Discussion (Misc queries) |