$ amount does not calculate with a space

[FXA]

My issue is when I copy and paste from my source, that source has no comma in the dollar amount when the dollar amount hits the thousands and above. Is there a way around this?

Formula:

=C5+AK8+AK9+AK10

C5 = Starting Balance

AK8 =SUM(N10:N1809)

AK9 =SUM(L10:L1809)

AK10 =SUM(M10:M1809)


Thank you in advance.

 

[awoohaw]

Can you check your formats of cells c5, the Currency Format puts the symbol next to the first numeral. The "Accounting" Format will put the symbol on the far left.

 

[FXA]

Can you check your formats of cells c5, the Currency Format puts the symbol next to the first numeral. The "Accounting" Format will put the symbol on the far left.

That part is ok. When I added a comma where the space is in this number

3 246.87

to

3,246.87

it works

 

[awoohaw]

So, are you good?

 

[FXA]

So, are you good?

No. The format I copy and paste from I cannot change so I am looking to have excel remove the space and/or replace the space with a comma automatically.

 

[awoohaw]

are you saying when you copy "1,234.56"... what goes into your cell is "1 234.56"?

Have you look at what it looks like in notepad? Is your computer picking up the comma?
Is the source data in excel? if so, what is the format there? And anyreason why you can't import using data/connection instead of a copy/paste?

 

[FXA]

are you saying when you copy "1,234.56"... what goes into your cell is "1 234.56"?

Have you look at what it looks like in notepad? Is your computer picking up the comma?
Is the source data in excel? if so, what is the format there? And anyreason why you can't import using data/connection instead of a copy/paste?

This is the data it is grabbing. Notice that there is a space in all numbers above 3 digits to the left of the decimal

 

[awoohaw]

Did you do what I asked and copy the raw data onto a notepad page? Is the data in excel? I'm seeing time formats with "." as the separator - can that be part of the problem?

 

[awoohaw]

And it seems like both photos you've posted are from excel worksheets. Not the original data. That is no help.

 

[FXA]

There are no formulas in these cells in these rows. (See first pic below)

The Formula from G5 is:
=C5+AK8+AK9+AK10
and it reads Column N starting on Row 10 which has no formulas

The issue is the space in between the 3 and the 2 in Column N in Row 10 because that is how it is copied using copy and paste.
(See 2nd pic below)
What I am looking for is a way to have Excel remove that space between the 3 and the 2 when it is copied over