Can someone please tell me how to set a certain amount of spaces between text
in a given cell? Here is what I have:

A1
Market Description
Tucson,AZ

What I want is:

A1
Market Descripton Tucson,AZ

All of the information is in cell A1. What I would also like to do after
setting the amount of spaces between each text, is move the second category
to the next cell so it will look like this:

A1 A2 A3
Market Description Tucson,AZ

Maybe I could skip the "spaces" issue and just figure out a way to take
whatever text is set more than 2 spaces and move it to the next cell? Any
answers?


--
Message posted via http://www.officekb.com

Hi Erik,

With the text cells selected, try:

Data | Text to columns | Delimited | Next | Check the 'Space' option |
Next | Finish.

To remove leading or trailing spaces, use Excel's Trim function.


---
Regards,
Norman



"Erik K via OfficeKB.com" <u13156@uwe wrote in message
news:56d93682c0ce1@uwe...
Can someone please tell me how to set a certain amount of spaces between
text
in a given cell? Here is what I have:

A1
Market Description
Tucson,AZ

What I want is:

A1
Market Descripton Tucson,AZ

All of the information is in cell A1. What I would also like to do after
setting the amount of spaces between each text, is move the second
category
to the next cell so it will look like this:

A1 A2 A3
Market Description Tucson,AZ

Maybe I could skip the "spaces" issue and just figure out a way to take
whatever text is set more than 2 spaces and move it to the next cell?
Any
answers?


--
Message posted via http://www.officekb.com

Does the question mean that if there is just one space you don't want to
split it? If not, you could use DataText To Columns delimited by space.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Erik K via OfficeKB.com" <u13156@uwe wrote in message
news:56d93682c0ce1@uwe...
Can someone please tell me how to set a certain amount of spaces between
text
in a given cell? Here is what I have:

A1
Market Description
Tucson,AZ

What I want is:

A1
Market Descripton Tucson,AZ

All of the information is in cell A1. What I would also like to do after
setting the amount of spaces between each text, is move the second
category
to the next cell so it will look like this:

A1 A2 A3
Market Description Tucson,AZ

Maybe I could skip the "spaces" issue and just figure out a way to take
whatever text is set more than 2 spaces and move it to the next cell?
Any
answers?


--
Message posted via http://www.officekb.com

Yes, if there is only one space I do not want to move it to another cell nor
delete the space. Any suggestions?

Bob Phillips wrote:
Does the question mean that if there is just one space you don't want to
split it? If not, you could use DataText To Columns delimited by space.

Can someone please tell me how to set a certain amount of spaces between text
in a given cell? Here is what I have:
[quoted text clipped - 18 lines]
whatever text is set more than 2 spaces and move it to the next cell? Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1

Norman,

How do I write this in a VBA macro?

Norman Jones wrote:
Hi Erik,

With the text cells selected, try:

Data | Text to columns | Delimited | Next | Check the 'Space' option |
Next | Finish.

To remove leading or trailing spaces, use Excel's Trim function.

---
Regards,
Norman

Can someone please tell me how to set a certain amount of spaces between
text
[quoted text clipped - 21 lines]
Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1

Hi Erik,

How do I write this in a VBA macro?

Turn on the macro recorder while you perform the operation manually. This
will provide you with base code which can be edited to render it suitable
for general application.

However, I note from your response to Bob, that you wish to distinguish
between single and multiple spaces.With the text selected, perform a
Search/Replace, replacing double spaces with a character such as # (hash).

Then perform the Text To Columns operation as before, this time un-checking
space and inserting the hash character in the 'Other' delimiter box.

Change double space to treble space or whatever is appropriate to your
circumstances.


---
Regards,
Norman



"Erik K via OfficeKB.com" <u13156@uwe wrote in message
news:56d9b5c70904d@uwe...
Norman,

How do I write this in a VBA macro?

Norman Jones wrote:
Hi Erik,

With the text cells selected, try:

Data | Text to columns | Delimited | Next | Check the 'Space' option |
Next | Finish.

To remove leading or trailing spaces, use Excel's Trim function.

---
Regards,
Norman

Can someone please tell me how to set a certain amount of spaces between
text
[quoted text clipped - 21 lines]
Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1

Where/how do I put the :

Data | Text to columns | Delimited | Next | Check the 'Space' option |
Next | Finish information?

Erik K wrote:
Norman,

How do I write this in a VBA macro?

Hi Erik,

[quoted text clipped - 14 lines]
Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1

Geez!

Nevermind that question. Ever feel stupid? lol

Erik K wrote:
Where/how do I put the :

Data | Text to columns | Delimited | Next | Check the 'Space' option |
Next | Finish information?

Norman,

[quoted text clipped - 5 lines]
Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1

Here is a macro

Sub MoveData()
Dim i As Long
Dim cell As Range

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "A").Value = Replace(Cells(i, "A").Value, " ", "|")
Next i
Columns("A:A").TextToColumns Destination:=Range("A1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
consecutiveDelimiter:=True, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=True, OtherChar:="|", _
FieldInfo:=Array(Array(1, 1), Array(2, 1),
_
Array(3, 1), Array(4, 1), Array(5, 1))

For Each cell In ActiveSheet.UsedRange
cell.Value = Trim(cell.Value)
Next cell

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Erik K via OfficeKB.com" <u13156@uwe wrote in message
news:56d9a5469aacb@uwe...
Yes, if there is only one space I do not want to move it to another cell
nor
delete the space. Any suggestions?

Bob Phillips wrote:
Does the question mean that if there is just one space you don't want to
split it? If not, you could use DataText To Columns delimited by space.

Can someone please tell me how to set a certain amount of spaces
between text
in a given cell? Here is what I have:
[quoted text clipped - 18 lines]
whatever text is set more than 2 spaces and move it to the next cell?
Any
answers?


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200511/1