+ Reply to Thread
Results 1 to 5 of 5

Replacing ' but maintaining zeros

  1. #1
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172

    Replacing ' but maintaining zeros

    Can some one advise me as to how I can overcome a problem that has recently occurred? I am sent an automatically created report as a .csv file, one of the columns contains both text and numbers, this is copied into another file where the data is concatenated with other information to create a batch file.

    I had thought this had been working fine until I found that numbers within the column, which had leading zeros, were now without them. To overcome this I had a leading ‘ (apostrophe) added to the column in the .csv file so it read as text. My problem now is how to I remove the ‘ but maintain the leading zeros when I copy the data over and ensure that the ' is not in the result when I have concatenated the data.

    I’ve looked at find and replace, substitute and VBA without success.

    Has anyone got a solution?

  2. #2
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845
    Tellm,

    If you format the cells as text then if you have "0001" it will stay "0001"
    Hope this is what you want.
    If not then you may need a macro to remove the " ' " and format the cell as text.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.

  3. #3
    Forum Contributor
    Join Date
    11-28-2005
    Location
    Dover, England
    Posts
    172
    The problem with this is that the apostrophe is also carried over and when I concatenate the data it becomes part of the concatenation and this becomes corrupted.

    I agree I need to somehow find and replace the ‘ but this is the sticking point as the find and replace doesn’t identify an ‘, it just returns a can’t find.

  4. #4
    Forum Expert Carim's Avatar
    Join Date
    04-07-2006
    Posts
    4,070
    Hi,

    Following code should help ...

    Sub ApostroRemove()
    For Each currentcell In Selection
    If currentcell.HasFormula = False Then
    currentcell.Formula = currentcell.Value
    End If
    Next
    End Sub

    HTH
    Cheers
    Carim

  5. #5
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845
    Tellm

    The follow code should also help.
    If it detects an apostrophe it should format that cell as text and remove the apostrophe.
    Not knowing your range of data I set the Columns to "A thru AA". You can change it to the number of columns you have.
    Copy this code to a module.
    Test on a copy of your data before you actually use it.


    Please Login or Register  to view this content.

+ 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