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

If your import has leading or trailing spaces and only one leftmost 0 in
cell A1, this will work: =RIGHT(A1,LEN(TRIM(A1)-1))

Tyro

"Kathleen Hogan" <Kathleen wrote in message
...
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

It didn't work. I got the infamous #value! error. Apparently the leading
space isn't actually a space, but I'm not sure what it is. When I view
hidden characters it appears as a superscript o (like a degree sign but in
front) instead of a space. It will let me manually delete it, but I can't
seem to remove it with a macro or formula.

Any ideas?

"Tyro" wrote:

If your import has leading or trailing spaces and only one leftmost 0 in
cell A1, this will work: =RIGHT(A1,LEN(TRIM(A1)-1))

Tyro

"Kathleen Hogan" <Kathleen wrote in message
...
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 interesting. The LEN function is returning a date back in 1900. Do the
cells always contain X0NNNNNNN?
tyro

"Kathleen Hogan" wrote in message
...
It didn't work. I got the infamous #value! error. Apparently the leading
space isn't actually a space, but I'm not sure what it is. When I view
hidden characters it appears as a superscript o (like a degree sign but in
front) instead of a space. It will let me manually delete it, but I can't
seem to remove it with a macro or formula.

Any ideas?

If the data came from the web, you could have those non-breaking HMTL spaces in
your cell.

David McRitchie has a macro that can help clean up:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Kathleen Hogan wrote:

It didn't work. I got the infamous #value! error. Apparently the leading
space isn't actually a space, but I'm not sure what it is. When I view
hidden characters it appears as a superscript o (like a degree sign but in
front) instead of a space. It will let me manually delete it, but I can't
seem to remove it with a macro or formula.

Any ideas?

"Tyro" wrote:

If your import has leading or trailing spaces and only one leftmost 0 in
cell A1, this will work: =RIGHT(A1,LEN(TRIM(A1)-1))

Tyro

"Kathleen Hogan" <Kathleen wrote in message
...
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




--

Dave Peterson

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

The formula worked!!!! Thank you.

"Ron Rosenfeld" wrote:

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

On Tue, 8 Jan 2008 05:04:00 -0800, Kathleen Hogan
wrote:

The formula worked!!!! Thank you.

You're welcome. Glad to help. Thanks for the feedback.
--ron