cell has junk in front of my data!???!

Hi everyone, I could use some direction. I have a work sheet (800 pages) and in column A has an apostrophe and spaces in front of each usable information. EXAMPLE: ' Trucking Company

Whats the easiest way to remove all this to keep it from corrupting my formulas?

Have you tried using Find & Replace

As per your example
Find what would = ' & a space/s
Replace With would be left blank

Under the Find & Replace Options Button you can also select other criteria like Bold Text.

Also Selecting the column A before using Find & Replace will limit tthe replacement to column A

When you say you have 800 pages is that all within one spreadsheet, muliple sheets within one workbook - how many sheets, or multiple workbooks with one or multiple sheets.

With multiple sheets or books then a macro may be a better solution to solving your problem.

How often does this need to be done will also have an inpact on the best method to tackle your problem

Originally Posted by mudraker

Have you tried using Find & Replace

As per your example
Find what would = ' & a space/s
Replace With would be left blank

Under the Find & Replace Options Button you can also select other criteria like Bold Text.

Also Selecting the column A before using Find & Replace will limit tthe replacement to column A

When you say you have 800 pages is that all within one spreadsheet, muliple sheets within one workbook - how many sheets, or multiple workbooks with one or multiple sheets.

With multiple sheets or books then a macro may be a better solution to solving your problem.

How often does this need to be done will also have an inpact on the best method to tackle your problem

I couldn't get the Find & Replace to work, but I'm not sure that I was doing it correctly. I used the "Replace" dialog box and put in the ' and tried to replace it with no spaces. I couldnt get the function to even find the ' thats what makes me think im doing something wrong. All of the cells that need to be changed are on the same sheet that is 800 pages.

You can try using a formula like:

=TRIM(A1)

and copy down.

(Note this may cause you some problems if you have intentional double spaces between words.)

You will need to copy and paste values back into your column from the formula column.

Hope this helps!

Try this macro

It replaces ' & space with nothing. for all entries in column A

wadeh2o suggestion of using trim will not work as it removes spaces at the start & end of entries.

If you are not familuar with macros

Copy every thing from Sub Macro1() to End Sub

Have the sheet with you data active
Press Alt & F11 Keys - this takes you to the Visual Basic Editor
From the InsertMenu < select Module
Past the macro code
Press F5 to run macro

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/12/2006 by NLJA
'

'
Columns("A:A").Select
Selection.Replace What:="' ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub