removing text characters from a cell

[volleygods]

I need to find an easy function or macro to remove all text characters from a
cell.
EX. Pail=19KG converts to 19

 

[Shane Devenshire]

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.

 

[Glenn]

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&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

Credit to Bob Phillips

 

[Gord Dibben]

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

 

[volleygods]

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

 

[Rick Rothstein]

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&"0123456789")),99),ROW($1:$99)))

 

[Rick Rothstein]

If the number you want **ALWAYS** follows an equal sign...

=LOOKUP(9.9E+307,--LEFT(MID(A1,FIND("=",A1)+1,99),ROW($1:$99)))

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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

 

[Gord Dibben]

Wow!!

So many things to consider other than the sample OP posted.


Thanks Ron

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

 

[Ron Rosenfeld]

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

 

[Jarek Kujawa]

Incorrect Gord

see Ron's answer...

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



- Poka¿ cytowany tekst -

 

[Gord Dibben]

Already noted Jarek

Thanks for the pointing-out.


Gord