On Mon, 7 Jan 2008 13:00:01 -0800, Kathleen Hogan <Kathleen
wrote:

I am trying to create a formula to remove the leading space and 0 from a
series of numbers imported into Excel from a Purchase Order. I have tried
using the trim function, but it didn't work. I could easily correct with the
replace function, but I want a formula that I can use repeatedly (some of the
POs can be quite large) to quickly clear the import error.

example:
I import:
01827245
02445313

I need:
1827245
2445313


How about

=--MID(A1,2,255)


If that doesn't work, you could use this UDF (user defined function)

To enter it, <alt-F11 opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens.

To use it, enter a formula like =v(cell_ref) in some cell (eg. =v(A1) )

It should return just the numeric value. Be sure your cell is formatted as
General or Number.

====================================
Option Explicit
Function v(str As String) As Double
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+"
Set mc = re.Execute(str)
v = Val(mc(0))
End Function
===========================

As written, the UDF will return the first group of numbers in the string; and
will return a VALUE error if there are no numbers in the string.

--
--ron