Hello,

I have an Excel Spreadsheet that has a column that lists 100s of song titles
but the first charactor in each cell in this column is a space.

Is there a way of removing these spaces automatically?

Many thanks
Gary

There are two way of removing spaces.

1) In the Edit Menu use replace and replace a space with nothing. This will
remove all spaces not just the one at the beginning of the line.

2) Add the formula below in a new column. Then use copy and paste special
(value only) to copy the data in the new column back to the original column.

=MID(A1,2,LEN(A1)) where A1 is the cell with the spaqce at the beginning.

"Gary" wrote:

Hello,

I have an Excel Spreadsheet that has a column that lists 100s of song titles
but the first charactor in each cell in this column is a space.

Is there a way of removing these spaces automatically?

Many thanks
Gary

Hi

Try selecting the column and going TextToColumns (available from the
Data menu) and choose Fixed WidthNext - insert a column stripping out
the first character (ensure you have no other columns inserted)Next
and on this tab choose not to import the first column (ie the space
character) and click Finish.

Richard


On 7 Jan, 11:34, Gary wrote:
Hello,

I have an Excel Spreadsheet that has a column that lists 100s of song titles
but the first charactor in each cell in this column is a space.

Is there a way of removing these spaces automatically?

Many thanks
Gary

Hi Joel, thank you!

1) In the Edit Menu use replace and replace a space with nothing. This will
remove all spaces not just the one at the beginning of the line.

I knew about this one but as you rightly said it will take away all spaces -
not good!


2) Add the formula below in a new column. Then use copy and paste special
(value only) to copy the data in the new column back to the original column.

=MID(A1,2,LEN(A1)) where A1 is the cell with the spaqce at the beginning.

This formula (and your instructions) worked a treat - thank you so much, you
saved me a very tedious job!

Thanks again,
Gary

for each c in range("a2:a102")
c.value=right(c,len(c)-1)
next c

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hello,

I have an Excel Spreadsheet that has a column that lists 100s of song
titles
but the first charactor in each cell in this column is a space.

Is there a way of removing these spaces automatically?

Many thanks
Gary

Here's another approach (VBA has a function LTrim (Left Trim))
Before running Macro Highlight All your songs, say they are listed
in the range B5:B105 - and then Run Macro. Songs without a
starting space character will not be effected.

Sub ElimSpace()
For Each c In Selection
Fixedname = LTrim(c.Value)
c.Value = Fixedname
Next c
End Sub


"Gary" wrote:

Hello,

I have an Excel Spreadsheet that has a column that lists 100s of song titles
but the first charactor in each cell in this column is a space.

Is there a way of removing these spaces automatically?

Many thanks
Gary

Hi Jim,

This looks like an interesting approach to my problem as I will need to do
this fairly regularly.

However, I have never used macros before and don not know where to place
your script? Or, indeed to use a macro!?

I've had a quick look and can't see where it should go?

Presumably I can save this 'macro' so I can just run it at a later date?

Many thanks
Gary

"Jim May" wrote:

Here's another approach (VBA has a function LTrim (Left Trim))
Before running Macro Highlight All your songs, say they are listed
in the range B5:B105 - and then Run Macro. Songs without a
starting space character will not be effected.

Sub ElimSpace()
For Each c In Selection
Fixedname = LTrim(c.Value)
c.Value = Fixedname
Next c
End Sub


"Gary" wrote:

Hello,

I have an Excel Spreadsheet that has a column that lists 100s of song titles
but the first charactor in each cell in this column is a space.

Is there a way of removing these spaces automatically?

Many thanks
Gary

Hi Jim, after some poking about with macros I worked out how it should be
done - thank you VERY much for the info. it will save me lots of time.

Thanks again
Gary

Hi Jim,

I have just realised there is probably an easier way to do this.

I am using a cmd prompt command to make a .txt file of all my karaoke music:

dir *.mp3 list.txt /s /b

But the results give me too much information:

c:\documents and settings\gary\my documents\my karaoke\ARTIST\ARTIST -
SONG (CDG).mp3

Thus far I have been opening this file with MS Word to 'find and replace'
"c:\documents and settings\gary\my documents\my karaoke\" and "_(CDG).mp3"
which leaves me with:

ARTIST\ARTIST - SONG

I then save it and open this new '.txt' file in Excel and use the "\" as the
delimiter so "ARTIST" is listed in column A and "ARTIST - SONG" is listed in
column B. I then deleted column A, and save the remaining "ARTIST - SONG"
information back to a .txt file.

I open this new file, but this time I use the "-" as the delimiter to give
"ARTIST" and "SONG" in columns A and B. (this is where my orginal question
came in about how to remove the spaces this created in the "SONGS" column).

Is there a way I can open the original list.txt file in excel and use a
macro to do all of this for me?

Many thanks
Gary

Why bother with that when this is one way to import a list of all mp3 files
into an excel worksheet.
Ian & Sylvia - Darcy Farrow.mp3
Ian & Sylvia - Early Morning Rain.mp3
Ian & Sylvia - Four Strong Winds.mp3
Ian & Sylvia - Last Thing On My Mind.mp3


Sub FindFilesA()
Application.ScreenUpdating = False
Dim FN As String
Dim ThisRow As Long
Dim FileLocation As String

'change below to suit
'=============
FileLocation = "c:\mymusic\*.mp3"
'==========
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hi Jim,

I have just realised there is probably an easier way to do this.

I am using a cmd prompt command to make a .txt file of all my karaoke
music:

dir *.mp3 list.txt /s /b

But the results give me too much information:

c:\documents and settings\gary\my documents\my karaoke\ARTIST\ARTIST -
SONG (CDG).mp3

Thus far I have been opening this file with MS Word to 'find and replace'
"c:\documents and settings\gary\my documents\my karaoke\" and "_(CDG).mp3"
which leaves me with:

ARTIST\ARTIST - SONG

I then save it and open this new '.txt' file in Excel and use the "\" as
the
delimiter so "ARTIST" is listed in column A and "ARTIST - SONG" is listed
in
column B. I then deleted column A, and save the remaining "ARTIST - SONG"
information back to a .txt file.

I open this new file, but this time I use the "-" as the delimiter to give
"ARTIST" and "SONG" in columns A and B. (this is where my orginal question
came in about how to remove the spaces this created in the "SONGS"
column).

Is there a way I can open the original list.txt file in excel and use a
macro to do all of this for me?

Many thanks
Gary

Hi Don, - thank you so much for the code.

Three steps away from perfection!

1... Is there a way to make it include subdirectories?

2... Can it separate out the file name so the artist and song are in columns
A and B?

3... Can it remove (like find and replace) the file extension?

I know this is a big ask - but it would be sooo much easier if I could find
away to do all this.

Many thanks again
Gary

"Don Guillett" wrote:

Why bother with that when this is one way to import a list of all mp3 files
into an excel worksheet.
Ian & Sylvia - Darcy Farrow.mp3
Ian & Sylvia - Early Morning Rain.mp3
Ian & Sylvia - Four Strong Winds.mp3
Ian & Sylvia - Last Thing On My Mind.mp3


Sub FindFilesA()
Application.ScreenUpdating = False
Dim FN As String
Dim ThisRow As Long
Dim FileLocation As String

'change below to suit
'=============
FileLocation = "c:\mymusic\*.mp3"
'==========
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hi Jim,

I have just realised there is probably an easier way to do this.

I am using a cmd prompt command to make a .txt file of all my karaoke
music:

dir *.mp3 list.txt /s /b

But the results give me too much information:

c:\documents and settings\gary\my documents\my karaoke\ARTIST\ARTIST -
SONG (CDG).mp3

Thus far I have been opening this file with MS Word to 'find and replace'
"c:\documents and settings\gary\my documents\my karaoke\" and "_(CDG).mp3"
which leaves me with:

ARTIST\ARTIST - SONG

I then save it and open this new '.txt' file in Excel and use the "\" as
the
delimiter so "ARTIST" is listed in column A and "ARTIST - SONG" is listed
in
column B. I then deleted column A, and save the remaining "ARTIST - SONG"
information back to a .txt file.

I open this new file, but this time I use the "-" as the delimiter to give
"ARTIST" and "SONG" in columns A and B. (this is where my orginal question
came in about how to remove the spaces this created in the "SONGS"
column).

Is there a way I can open the original list.txt file in excel and use a
macro to do all of this for me?

Many thanks
Gary

Gary

Give a try to DataText to ColumnsDelimited by hyphen(-)

Ian & Sylvia Darcy Farrow.mp3 into Column A and B

Then DataText to columnsDelimited by period(.)

Note the step where you can "skip" a column


Gord Dibben MS Excel MVP

On Tue, 8 Jan 2008 12:01:05 -0800, Gary wrote:

Hi Don, - thank you so much for the code.

Three steps away from perfection!

1... Is there a way to make it include subdirectories?

2... Can it separate out the file name so the artist and song are in columns
A and B?

3... Can it remove (like find and replace) the file extension?

I know this is a big ask - but it would be sooo much easier if I could find
away to do all this.

Many thanks again
Gary

"Don Guillett" wrote:

Why bother with that when this is one way to import a list of all mp3 files
into an excel worksheet.
Ian & Sylvia - Darcy Farrow.mp3
Ian & Sylvia - Early Morning Rain.mp3
Ian & Sylvia - Four Strong Winds.mp3
Ian & Sylvia - Last Thing On My Mind.mp3


Sub FindFilesA()
Application.ScreenUpdating = False
Dim FN As String
Dim ThisRow As Long
Dim FileLocation As String

'change below to suit
'=============
FileLocation = "c:\mymusic\*.mp3"
'==========
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hi Jim,

I have just realised there is probably an easier way to do this.

I am using a cmd prompt command to make a .txt file of all my karaoke
music:

dir *.mp3 list.txt /s /b

But the results give me too much information:

c:\documents and settings\gary\my documents\my karaoke\ARTIST\ARTIST -
SONG (CDG).mp3

Thus far I have been opening this file with MS Word to 'find and replace'
"c:\documents and settings\gary\my documents\my karaoke\" and "_(CDG).mp3"
which leaves me with:

ARTIST\ARTIST - SONG

I then save it and open this new '.txt' file in Excel and use the "\" as
the
delimiter so "ARTIST" is listed in column A and "ARTIST - SONG" is listed
in
column B. I then deleted column A, and save the remaining "ARTIST - SONG"
information back to a .txt file.

I open this new file, but this time I use the "-" as the delimiter to give
"ARTIST" and "SONG" in columns A and B. (this is where my orginal question
came in about how to remove the spaces this created in the "SONGS"
column).

Is there a way I can open the original list.txt file in excel and use a
macro to do all of this for me?

Many thanks
Gary

I haven't found a way for DIR to get sub folders....

This will get the files in sub directories along with the directory name. I
understand that filesearch does NOT work with xl2007. You will need to
split em out.

Sub GetFileList()
Dim iCtr As Integer

With Application.FileSearch
.NewSearch
.LookIn = "c:\mymusic"
.SearchSubFolders = True
.Filename = ".mp3"
If .Execute 0 Then
For iCtr = 1 To .FoundFiles.Count
Cells(iCtr, 1).Value = .FoundFiles(iCtr)
Next iCtr
End If
End With
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hi Don, - thank you so much for the code.

Three steps away from perfection!

1... Is there a way to make it include subdirectories?

2... Can it separate out the file name so the artist and song are in
columns
A and B?

3... Can it remove (like find and replace) the file extension?

I know this is a big ask - but it would be sooo much easier if I could
find
away to do all this.

Many thanks again
Gary

"Don Guillett" wrote:

Why bother with that when this is one way to import a list of all mp3
files
into an excel worksheet.
Ian & Sylvia - Darcy Farrow.mp3
Ian & Sylvia - Early Morning Rain.mp3
Ian & Sylvia - Four Strong Winds.mp3
Ian & Sylvia - Last Thing On My Mind.mp3


Sub FindFilesA()
Application.ScreenUpdating = False
Dim FN As String
Dim ThisRow As Long
Dim FileLocation As String

'change below to suit
'=============
FileLocation = "c:\mymusic\*.mp3"
'==========
FN = Dir(FileLocation)
Do Until FN = ""
ThisRow = ThisRow + 1
Cells(ThisRow, 1) = FN
FN = Dir
Loop
Application.ScreenUpdating = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hi Jim,

I have just realised there is probably an easier way to do this.

I am using a cmd prompt command to make a .txt file of all my karaoke
music:

dir *.mp3 list.txt /s /b

But the results give me too much information:

c:\documents and settings\gary\my documents\my
karaoke\ARTIST\ARTIST -
SONG (CDG).mp3

Thus far I have been opening this file with MS Word to 'find and
replace'
"c:\documents and settings\gary\my documents\my karaoke\" and
"_(CDG).mp3"
which leaves me with:

ARTIST\ARTIST - SONG

I then save it and open this new '.txt' file in Excel and use the "\"
as
the
delimiter so "ARTIST" is listed in column A and "ARTIST - SONG" is
listed
in
column B. I then deleted column A, and save the remaining "ARTIST -
SONG"
information back to a .txt file.

I open this new file, but this time I use the "-" as the delimiter to
give
"ARTIST" and "SONG" in columns A and B. (this is where my orginal
question
came in about how to remove the spaces this created in the "SONGS"
column).

Is there a way I can open the original list.txt file in excel and use a
macro to do all of this for me?

Many thanks
Gary

Hi Don,

Thank you so much for all your time and help with this - it is very much
appreciated.

This latest coding you have supplied mixed with Gord's "text to column"
(which i did know about before) and a bit of a self teach crash course on
macros has made everything come together very nicely!

Thank you again
Gary

Hi Gord, Thank you for that - I was wondering where that function was!

Using a mix of Don's and your advice I have managed to setup 2 macros that
sorts everything just the way I want it - so thank you very much.

Gary

As with Gord, I'm glad to help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gary" wrote in message
...
Hi Don,

Thank you so much for all your time and help with this - it is very much
appreciated.

This latest coding you have supplied mixed with Gord's "text to column"
(which i did know about before) and a bit of a self teach crash course on
macros has made everything come together very nicely!

Thank you again
Gary