| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Removing Space
Hello - When I import data, there is a space in front of the first
character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
Tom wrote:
Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom ltrim? beege |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
Take a look at LTrim.
If you want to get right of the rightmost space characters, you'd use RTrim. If you wanted to get rid of both leading and trailing spaces, you could use Trim. And if you wanted to get rid of leading/trailing/duplicated internal spaces, you could use application.trim. Tom wrote: Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
On Feb 26, 11:31 am, Dave Peterson wrote:
Take a look at LTrim. If you want to get right of the rightmost space characters, you'd use RTrim. If you wanted to get rid of both leading and trailing spaces, you could use Trim. And if you wanted to get rid of leading/trailing/duplicated internal spaces, you could use application.trim. Tom wrote: Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom -- Dave Peterson Could you please put in code format. I'm not very real versed in writing visual basic code. Thanks, Tom |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
Option Explicit
Sub testme() dim myRng as range dim myCell as range with worksheets("sheet9999") set myrng = .range("a1",.cells(.rows.count,"A").end(xlup)) end with for each mycell in myrng.cells mycell.value = ltrim(mycell.value) 'or mycell.value = rtrim(mycell.value) 'or mycell.value = trim(mycell.value) 'or mycell.value = application.trim(mycell.value) next mycell end sub Tom wrote: On Feb 26, 11:31 am, Dave Peterson wrote: Take a look at LTrim. If you want to get right of the rightmost space characters, you'd use RTrim. If you wanted to get rid of both leading and trailing spaces, you could use Trim. And if you wanted to get rid of leading/trailing/duplicated internal spaces, you could use application.trim. Tom wrote: Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom -- Dave Peterson Could you please put in code format. I'm not very real versed in writing visual basic code. Thanks, Tom -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
Spaces don't normally occur in Excel cells except in text entries. If your
problem is that they are really spaces, you can remove them with the Replace function. Search for (type in a space) and Replace with (nothing--leave box empty). Be sure not to delete spaces in the text entries. If they aren't really spaces entered with the spacebar, you may have to look for a different number format (Format-Cells). "Tom" wrote in message ups.com... Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
On Feb 26, 1:58 pm, "Richard O. Neville"
wrote: Spaces don't normally occur in Excel cells except in text entries. If your problem is that they are really spaces, you can remove them with the Replace function. Search for (type in a space) and Replace with (nothing--leave box empty). Be sure not to delete spaces in the text entries. If they aren't really spaces entered with the spacebar, you may have to look for a different number format (Format-Cells). "Tom" wrote in message ups.com... Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom- Hide quoted text - - Show quoted text - I got Dave's code to run (though chugs a little???). When I tried using the "Find Replace" it removed all spaces. Just wanted the first spaces removed. Thanks to both of you for helping me. Much appreciated. Tom |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Removing Space
If your data is in a single column, and you could get rid of leading and
trailing spaces, you could use: Select the column Data|Text to columns Fixed width (Remove any lines that excel guessed) and finish up If you have lots of columns, you can do the same thing for each column. Record a macro when you test it manually to see the code. (This'll be a lot quicker.) Tom wrote: On Feb 26, 1:58 pm, "Richard O. Neville" wrote: Spaces don't normally occur in Excel cells except in text entries. If your problem is that they are really spaces, you can remove them with the Replace function. Search for (type in a space) and Replace with (nothing--leave box empty). Be sure not to delete spaces in the text entries. If they aren't really spaces entered with the spacebar, you may have to look for a different number format (Format-Cells). "Tom" wrote in message ups.com... Hello - When I import data, there is a space in front of the first character in a cell. Would like to know if there is a visual basic code to remove this space. I already have a code which removes all spaces, now need code to remove the first space and leaves the other spaces in place. Thanks, Tom- Hide quoted text - - Show quoted text - I got Dave's code to run (though chugs a little???). When I tried using the "Find Replace" it removed all spaces. Just wanted the first spaces removed. Thanks to both of you for helping me. Much appreciated. Tom -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Removing Space | Excel Discussion (Misc queries) | |||
| NEED HELP-----Removing a space at the end of a string of character | Excel Discussion (Misc queries) | |||
| removing a space between words in a cell | Excel Worksheet Functions | |||
| Removing a space after a comma | New Users to Excel | |||
| Removing a space within a cell | Excel Worksheet Functions | |||
Linear Mode
