+ Reply to Thread
Results 1 to 6 of 6

Comma delimited numbers with space after comma

  1. #1
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Comma delimited numbers with space after comma

    Hello all,

    Am having trouble with cell formatting (if that is the right description). I have imported data from another program and the figures in the columns are comma delimited and there is a space after the comma. the cells are not formatted as text as when i delete the comma (,) and add a point (.) and delete the space i am able to do calculations as desired. Given the range is quite large manual replacement is not suitable. a few examples of numbers are 175, 35, which i would like to read as 175.35, 35, 60 that i would like to read as 35.6... I have tried to change the delimiters via advanced options this did not work. Can anyone point me in the right direction. thanks in advance for your help.

    Regards
    Dan

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,905

    Re: Comma delimited numbers with space after comma

    Since you're delimiting the values separated by a comma, I'm assuming the two values are in separate cells? So 35, could be in A1, and _60 (underscore being a space) would be in B1?
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Comma delimited numbers with space after comma

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  4. #4
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Comma delimited numbers with space after comma

    Thank you for the replies and sorry for my tardiness. Please see attached a sample of my data that i would like to sort into other workbooks though as previously mentioned think it is the formatting that is preventing me from doing so. thanks again for your help.

    Regards
    Dan
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Comma delimited numbers with space after comma

    I would like to to use vlookup to extract data from this table. I am using Excel 2010 english version. I have looked at compatibility issues between french and english versions though this doesn't appear to be the problem. So imagine a list of these products manually created in my version of excel with quantity, CA etc. i would use vlookup to enter the data. Hope this is clear. If you have any questions let me know. Again i appreciate the help.

    Regards
    Dan

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Comma delimited numbers with space after comma

    To convert your values in columns D and E to number values with a decimal, use the following. Enter in N2 and fill across to O2 and fill down. Copy and paste values back into column D and E. You will now be dealing with numbers.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You can also click in A2, then click on the View tab, Freeze Panes, Freeze Top Row. This will keep the column headers visible while you scroll down the data. This will allow you to delete row 43 which has a copy of the column headers as it will no longer be necessary.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compare two cells with comma delimited numbers and display the numbers that are NOT duplic
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2014, 02:22 PM
  2. [SOLVED] Sort with comma delimited numbers
    By werko in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-08-2014, 08:49 PM
  3. Splitting comma delimited numbers into new columns
    By salmansohail in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2010, 07:00 PM
  4. Splitting comma delimited numbers into new columns
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-23-2009, 07:30 PM
  5. Adding comma delimited numbers in a cell
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-20-2009, 01:23 PM

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