Removing Spaces

[Gary]

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

 

[Joel]

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.

 

[RichardSchollar]

Hi

Try selecting the column and going TextToColumns (available from the
Data menu) and choose Fixed Width>Next - 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

 

[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

 

[Don Guillett]

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

 

[Jim May]

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]

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

 

[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

 

[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

 

[Don Guillett]

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

 

[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

 

[Gord Dibben]

Gary

Give a try to Data>Text to Columns>Delimited by hyphen(-)

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

Then Data>Text to columns>Delimited by period(.)

Note the step where you can "skip" a column


Gord Dibben MS Excel MVP

 

[Don Guillett]

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

 

[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

 

[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

 

[Gord Dibben]

Good to hear.

Thanks for the feedback

Gord

 

[Don Guillett]

As with Gord, I'm glad to help.