Comma delimited numbers with space after comma

[Cicada]

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

[mcmahobt]

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?

[Debraj Roy]

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

[Cicada]

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

[Cicada]

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

[newdoverman]

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.

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.