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
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.
Originally Posted by mudraker
Are your ' bits formatted in? Check custom format.
You can try using a formula like:
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!
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.
Select the cells, go to View > Find and type in ' followed by a space, click the Replace box, then Replace All.
See if that works
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
' Macro1 Macro
' Macro recorded 10/12/2006 by NLJA
Selection.Replace What:="' ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1