How to remove space between number and replace with period

[navids]

i have a column of numbers ie 100 90 i want it to read 100.90 so i can add the column at the bottom. THank

[63falcondude]

If A1 is "100 90", you can use this formula to result in "100.90"

=SUBSTITUTE(A1," ",".")+0

[navids]

Thank You, exactly what i was looking for, but there is just one thing left and i hope you can help since there were empty cells in between the column once i put your formula there is "value" showing up in the cells and i am still unable to do the total SUM of the column Thank you

[Pete_UK]

You can use this formula:

=--SUBSTITUTE(TRIM(A1)," ",".")

If the space is a non-breaking space character (code 160) you can use this instead:

=--SUBSTITUTE(A1,CHAR(160),".")

Copy down as required.

Hope this helps.

Pete

[navids]

Thanks it worked perfect, except when i try to SUM the total of the column i get a total of 0. Thanks

[63falcondude]

Thanks it worked perfect, except when i try to SUM the total of the column i get a total of 0. Thanks

You're welcome.

If you have your numbers with the spaces in column A, you would put one of the suggested formulas into another column (let's say column B) and then take the sum of column B.

[FDibbins]

...If you have your numbers with the spaces in column A...

Then that is not a number, it is text. But you are correct that either of the above formulas should fix that

[navids]

It's a number! the formula works fine, and I still have the issue unable to total the SUM of the column!

[63falcondude]

Then that is not a number, it is text. But you are correct that either of the above formulas should fix that

When I said "numbers with the spaces" I simply meant a cell with numbers and a space in it i.e. "## ##".

0,1,2,3,4,5,6,7,8, and 9 are numbers no matter how the cell that contains them is formatted.

Yes, a cell with numbers can be seen as a text string to Excel and (as you know) this is the case when there is a space between the numbers.

Formatting does not change the fact that the cell has numbers in it.

Arguing semantics.

It's a number! the formula works fine, and I still have the issue unable to total the SUM of the column!

Consider creating a small example of your issue along with the desired result of the formula/s to share with us.

You can upload a workbook directly to your post.

[navids]

I get to the point where the space is removed and the period is added in a column next to the original column, the column is formatted to "number".
But it still will not do a "Sum" of the new corrected column.
i.e column A 100 89 changes to column B 100.89 exactly like i want but when i try to Sum total of column B i get 0. Not the desired Result. Help and Thanks

[jindon]

i have a column of numbers ie 100 90 i want it to read 100.90 so i can add the column at the bottom. THank

Select the cell(s) that you want to apply.
Ctrl + H : Find " " and . in Replace then Replace All

[jindon]

Select Column A.
Hit Ctrl + H
Enter a space in Find
Enter a period in Replace and hit ReplaceAll.

This should make cColumn A as number.

[navids]

Screen Shot 2017-05-27 at 11.37.05 AM copy.jpg Attached is the image, Thanks

[buran]

could you check number formatting in column AT. I suspect it is some weird custom number format
or upload sample workbook.

[navids]

I have used =SUBSTITUTE(AT66," ",".")+0 works great thanks, except i am unable to SUM the total in Column AU

[63falcondude]

I have used =SUBSTITUTE(AT66," ",".")+0 works great thanks, except i am unable to SUM the total in Column AU

Try

=IFERROR(SUBSTITUTE(AT66," ",".")+0,"")

This will change the errors to blank and allow you to take the sum of the column.

[cytop]

As mentioned, upload a sample workbook, otherwise you'll be going around in circles all night