I have a set of numbers with text in them and I need to remove the text.

32h
320h
4206h

The H is always last but has different numbers of digits before it. Its a
column of data I need to group and sum. The data comes from a query and is
refreshed frequently so the "h" has to be removed every time the query
refreshes. I planned to put a formula in a column right beside the query
data but can't figure out how to write the formula because the H has
different numbers of digits before it.

How can I do this?


Todd

Try this:

=LEFT(A1,LEN(A1)-1)

HTH,
Elkar


"Todd" wrote:

I have a set of numbers with text in them and I need to remove the text.

32h
320h
4206h

The H is always last but has different numbers of digits before it. Its a
column of data I need to group and sum. The data comes from a query and is
refreshed frequently so the "h" has to be removed every time the query
refreshes. I planned to put a formula in a column right beside the query
data but can't figure out how to write the formula because the H has
different numbers of digits before it.

How can I do this?


Todd

I you need to sum the numbers after you get rid of the text I would suggest
using

=VALUE(LEFT(A1,LEN(A1)-1))

"Elkar" wrote:

Try this:

=LEFT(A1,LEN(A1)-1)

HTH,
Elkar


"Todd" wrote:

I have a set of numbers with text in them and I need to remove the text.

32h
320h
4206h

The H is always last but has different numbers of digits before it. Its a
column of data I need to group and sum. The data comes from a query and is
refreshed frequently so the "h" has to be removed every time the query
refreshes. I planned to put a formula in a column right beside the query
data but can't figure out how to write the formula because the H has
different numbers of digits before it.

How can I do this?


Todd

Todd, you could also use this VBA Function,

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

Hope it helps
Regards from Brazil going to the 6th world championship
Marcelo



"Todd" escreveu:

I have a set of numbers with text in them and I need to remove the text.

32h
320h
4206h

The H is always last but has different numbers of digits before it. Its a
column of data I need to group and sum. The data comes from a query and is
refreshed frequently so the "h" has to be removed every time the query
refreshes. I planned to put a formula in a column right beside the query
data but can't figure out how to write the formula because the H has
different numbers of digits before it.

How can I do this?


Todd