Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I trade files with ARCGIS and import *.dbf files, example attached. I need to be able to convert the non-contiguous numbers stored as text to numbers. If I simply edit(F2)/return or select a column and do a text to columns, tab delimited, the text is converted. The help menu suggests multiplying a range by 1, but then my blank cells have a zero and I cannot have any extraneous zeros in the data set. And as mentioned, the data is non-contiguous so selecting a range and converting to a number doesn’t work either.

    I could set up a macro to sequentially edit/return or some such, but I was wondering if there was a more elegant solution?

    Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Copy a blank cell
    Select the range
    Edit - paste special - Add
    it converts the text-numbers to number, but keeps the blanks cells

    Steve

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    THANK YOU.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    That is definitely very slick.

    NICE !
    Andrew

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Steve,

    Cool...Gets a Thunbs-UP from me.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I have this issue as well but I'm sorry I didn't understand

    "Copy a blank cell"

    Copy a blank cell to where?

    "Select the range"

    I assume the range of numbers that are TEXT. Is that correct?


    "Edit - paste special - Add"

    Paste special - add to where?

    Sorry, I'm not getting this>

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Copy a blank cell to where?
    Copy is a "from" not a "to". Paste (or paste-special) is the "to" part - that is done later.
    Copy a blank cell is just:
    Select a blank cell in the workbook
    Select "Edit" from the toolbar
    Then choose "Copy" from the submenu

    I assume the range of numbers that are TEXT. Is that correct?
    Yes

    Paste special - add to where?
    You paste-special what you copied (the blank cell) to the cells that are selected (the text to convert)

    After you select the text-cells you want to convert to numbers

    "Edit - paste special - Add" means:
    Select "Edit" from the toolbar
    and then choose "Paste special" from the submenu
    In the "Paste Special" dialog box
    Select the "Add" (in the "operation section")
    Then press the [ok] button

    Steve

Posting Permissions

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