If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Removing Leading Spaces
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 |
#2
|
|||
|
|||
Removing Leading Spaces
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 |
#3
|
|||
|
|||
Removing Leading Spaces
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 |
#4
|
|||
|
|||
Removing Leading Spaces
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? |
#5
|
|||
|
|||
Removing Leading Spaces
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 |
#6
|
|||
|
|||
Removing Leading Spaces
|
#7
|
|||
|
|||
Removing Leading Spaces
|
#8
|
|||
|
|||
Removing Leading Spaces
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 |
Thread Tools | |
Display Modes | |
|
|