+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    10-19-2006
    Posts
    9

    Unhappy 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?

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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

  3. #3
    Registered User
    Join Date
    10-19-2006
    Posts
    9
    Quote 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.

  4. #4
    Registered User
    Join Date
    11-30-2006
    Location
    UK
    MS-Off Ver
    Microsoft Office XP
    Posts
    61
    Are your ' bits formatted in? Check custom format.

    Jason

  5. #5
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    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!

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,099
    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
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  7. #7
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1