Setting spaces between text in a cell

[Erik K via OfficeKB.com]

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?

 

[Norman Jones]

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.

 

[Bob Phillips]

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

--

HTH

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

 

[Erik K via 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?

Does the question mean that if there is just one space you don't want to
split it? If not, you could use Data>Text 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?

 

[Erik K via OfficeKB.com]

Norman,

How do I write this in a VBA macro?

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?

 

[Norman Jones]

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

Norman,

How do I write this in a VBA macro?

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?

 

[Erik K via OfficeKB.com]

Where/how do I put the :

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

Norman,

How do I write this in a VBA macro?

Hi Erik,

[quoted text clipped - 14 lines]

 

[Erik K via OfficeKB.com]

Geez!

Nevermind that question. Ever feel stupid? lol

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]

 

[Bob Phillips]

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)

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

Does the question mean that if there is just one space you don't want to
split it? If not, you could use Data>Text 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?