removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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

removing text characters from a cell
 

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 -

removing text characters from a cell
 

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 -