Removing Leading Spaces


K

Kathleen Hogan

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
 
Ad

Advertisements

T

Tyro

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
 
K

Kathleen Hogan

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?
 
T

Tyro

How interesting. The LEN function is returning a date back in 1900. Do the
cells always contain X0NNNNNNN?
tyro
 
R

Ron Rosenfeld

On Mon, 7 Jan 2008 13:00:01 -0800, Kathleen Hogan <Kathleen
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.
 
Ad

Advertisements

K

Kathleen Hogan

The formula worked!!!! Thank you.

Ron Rosenfeld said:
On Mon, 7 Jan 2008 13:00:01 -0800, Kathleen Hogan <Kathleen



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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top