$ 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]

Just wanted a formula or code to have this done automatically is all. Guess I'll need to go to the other forum.

you have two people here who are trying to help you. but you won't help them help you.

 

[MARK858]

Just wanted a formula or code to have this done automatically is all. Guess I'll need to go to the other forum.

Does the end user know what to do with a formula?
If you want a formula then try

=SUBSTITUTE(N10," ","")+0

As for code as I said all you need to do is record yourself doing the Find and Replace manually and it will give you the code

 

[FXA]

And I asked if that source data was an excel spreadsheet as well, which you did not answer. If it is, then you have a problem with your source not your workbook.

I had already said it was from another source and that the source data being copied was the issue

 

[MARK858]

Please see post 22

 

[FXA]

Does the end user know what to do with a formula?
If you want a formula then try

=SUBSTITUTE(N10," ","")+0

As for code as I said all you need to do is record yourself doing the Find and Replace manually and it will give you the code

I am aware of this method but these are people who have no clue about Excel. Is there a VB way to do this?

 

[MARK858]

I am aware of this method but these are people who have no clue about Excel. Is there a VB way to do this?

As I said you can record it yourself but the code is

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("N:N").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Just wanted a formula or code

Why did you ask for a formula if you didn't want one?

 

[FXA]

?

 

[awoohaw]

well, you have two options. Get the folks giving you the source to provide it correctly. or you'll be stuck cleaning their data with one of the two solutions mark gave you if you can't get your end users to do it.

 

[MARK858]

@FXA see post 26

 

[FXA]

As I said you can record it yourself but the code is

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("N:N").Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
End Sub

Why did you ask for a formula if you didn't want one?

I meant a formula to embed in the cells previously without making that extra column then doing a copy n paste ect... but will try this VB Script.