Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Spaces within text
Hi All,
I need to write some code to solve this problem. I have a column of entries that I need to search through, and if it finds any cells that have entries with a space - then remove the space and join together the text on either side of the space. For example: abc123 def 456 ghi 789 jkl .... the result in the column would then be abc123 def456 ghi789jkl Thanks very much for your help Karen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Spaces within text
Use the =SUBSTUTUTE() function:
=SUBSTITUTE(B7," ","") will the remove the spaces in B7, for example -- Gary's Student "KAREN27" wrote: Hi All, I need to write some code to solve this problem. I have a column of entries that I need to search through, and if it finds any cells that have entries with a space - then remove the space and join together the text on either side of the space. For example: abc123 def 456 ghi 789 jkl ... the result in the column would then be abc123 def456 ghi789jkl Thanks very much for your help Karen |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Spaces within text
Hi Karen,
The code below should sort out your problem: Columns("I:I").Select Range("I57").Activate Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Where "I:I" is the column containing your data. Hope it helps. Cheers Blaise "KAREN27" wrote: Hi All, I need to write some code to solve this problem. I have a column of entries that I need to search through, and if it finds any cells that have entries with a space - then remove the space and join together the text on either side of the space. For example: abc123 def 456 ghi 789 jkl ... the result in the column would then be abc123 def456 ghi789jkl Thanks very much for your help Karen |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Spaces within text
select the range of cells and do
Edit=Replace What: put in a space (hit the space bar once) With: leave blank Click replace all. If you need a macro, turn on the macro recorder while you do it manually. -- Regards, Tom Ogilvy "KAREN27" wrote in message ... Hi All, I need to write some code to solve this problem. I have a column of entries that I need to search through, and if it finds any cells that have entries with a space - then remove the space and join together the text on either side of the space. For example: abc123 def 456 ghi 789 jkl ... the result in the column would then be abc123 def456 ghi789jkl Thanks very much for your help Karen |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Spaces within text
So how do you remove a space from an end of a string.
I need if a string ends with a space. Delete/remove it. If string has a character other than a spac,e leave it alone. -- Thank you for your time. Windows XP Office 2002 "BlaiseW" wrote: Hi Karen, The code below should sort out your problem: Columns("I:I").Select Range("I57").Activate Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Where "I:I" is the column containing your data. Hope it helps. Cheers Blaise "KAREN27" wrote: Hi All, I need to write some code to solve this problem. I have a column of entries that I need to search through, and if it finds any cells that have entries with a space - then remove the space and join together the text on either side of the space. For example: abc123 def 456 ghi 789 jkl ... the result in the column would then be abc123 def456 ghi789jkl Thanks very much for your help Karen |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing Spaces within text
You have a few options...
You could use: dim myCell as range dim myRng as Range set myrng = selection for each mycell in myrng.cells mycell.value = rtrim(mycell.value) next mycell or getting rid of leading and trailing: mycell.value = trim(mycell.value) or getting rid of leading/trailing/duplicated embedded spaces: mycell.value = application.trim(mycell.value) ====== A quick way way to get rid of leading/trailing spaces is to do: select the range data|text to columns fixed width (remove any lines that excel guessed at) finish up. If you need a macro, record one when you do it manually. James Kendall wrote: So how do you remove a space from an end of a string. I need if a string ends with a space. Delete/remove it. If string has a character other than a spac,e leave it alone. -- Thank you for your time. Windows XP Office 2002 "BlaiseW" wrote: Hi Karen, The code below should sort out your problem: Columns("I:I").Select Range("I57").Activate Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Where "I:I" is the column containing your data. Hope it helps. Cheers Blaise "KAREN27" wrote: Hi All, I need to write some code to solve this problem. I have a column of entries that I need to search through, and if it finds any cells that have entries with a space - then remove the space and join together the text on either side of the space. For example: abc123 def 456 ghi 789 jkl ... the result in the column would then be abc123 def456 ghi789jkl Thanks very much for your help Karen -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Spaces | Excel Discussion (Misc queries) | |||
removing all spaces | Excel Discussion (Misc queries) | |||
Removing spaces in cell | Excel Worksheet Functions | |||
removing spaces | Excel Discussion (Misc queries) | |||
removing spaces between the numbers | Excel Discussion (Misc queries) |