PDA

View Full Version : Solved: Can text be copied out of a cell to another cell?



Nosstech
08-01-2008, 08:06 AM
Is there a way to seperate a text string into different cells?
Below is an example that I have. All the data is dumped into Col. A

***EXAMPLE***

Country Radio Aug 08-01-sugarland-all i want to do
Country Radio Aug 08-02-george strait-troubadour (album version)
Country Radio Aug 08-03-rascal flatts-bob that head
Country Radio Aug 08-04-jessica simpson-come on over
Country Radio Aug 08-05-toby keith-she never cried in front of me

***RESULT DESIRED - I want the Album, track, artist and song title to be in different columns***
* | = column break
Country Radio Aug 08 | 01 | sugarland | all i want to do
Country Radio Aug 08 | 02 | george strait | troubadour (album version)
Country Radio Aug 08 | 03 | rascal flatts | bob that head
Country Radio Aug 08 | 04 | jessica simpson | come on over
Country Radio Aug 08 | 05 | toby keith | she never cried in front of me

kiyiya
08-01-2008, 08:14 AM
Not a VBA solution, but you can select column "A" and use text to columns with a delimiter of "space" to achieve your goals.

kiyiya
08-01-2008, 08:26 AM
Try this for a VBA solution:
Private Sub TextToColumns_Click()
Sheet1.Columns("A:A").Select
Selection.TextToColumns DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Space:=True
End Sub


I put this code on an active X button.

Nosstech
08-01-2008, 09:21 AM
Try this for a VBA solution:
Private Sub TextToColumns_Click()
Sheet1.Columns("A:A").Select
Selection.TextToColumns DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, Space:=True
End Sub


I put this code on an active X button.

How do I do that using "-" as the delimiter?

RonMcK
08-01-2008, 09:38 AM
Try removing 'Space:=True' and replacing with 'Other:=True, OtherChar:="-" ' (without the single quotes).

Cheers,

Nosstech
08-01-2008, 10:31 AM
Try removing 'Space:=True' and replacing with 'Other:=True, OtherChar:="-" ' (without the single quotes).

Cheers,

That worked! Is there a way I can use that code without a command button?

mdmackillop
08-01-2008, 10:38 AM
You can do it manually using Data/Text to Columns on the toolbar, or assign a keyboard shortcut to the macro.