How to get rid of dollar symbols

[bschwartz]

Hello,

The issue I am having comes from copying and pasting values from my online bank statement. I have a running balance that I copied to Excel for analysis. Excel cannot calculate formulas using these numbers since there is a $ symbol attached to each cell that was copied and pasted and the values do not seem to be recognized by Excel as numbers. Do you have any solution or ideas pertaining to this, or do I need to manually remove those symbols to enable calculation?

Excel version: 2010
Formula I tried to enter in cell F3: =F2-D3+E3
See example via link below.

Your help is greatly appreciated.

(sorry if this the wrong way to post a picture, but I could not figure out how else to attach or incorporate it into the post).
https://docs.google.com/document/d/1RM6Ywu0Aht0n_AozpXVbc8T4LybvqTl2OH-LtIBYYhQ/edit

Ben

 

[Michael M]

I can view the picture....but you could always clean them up in an adjacent column use

=TRIM(SUBSTITUTE(A2,"$",""))

and copy down
Don't forget to format as General

 

[bschwartz]

I can view the picture....but you could always clean them up in an adjacent column use

=TRIM(SUBSTITUTE(A2,"$",""))

and copy down
Don't forget to format as General

You're a genius. That did remove the symbols very quickly and easily. Unfortunately, it seems I had the wrong diagnosis. All values are under general formatting and the $ signs have been removed, but formulas are still not working.

To give you more information, I have attached a Google Spreadsheet where the same results occur. Google explains the error more clearly by stating that "$453.07" is not a number. For the record, the number in row 2 were added by me. All others were pasted.

https://docs.google.com/spreadsheet/ccc?key=0AtdluvNGu5AwdGpianNTVjVaUTFNWDd2MEJKX2FEa2c

Thanks for your help!

 

[Michael M]

Doh.....sorry, I meant format as Currency.
Whilever, the value in the cell is on the LHand side of the cell, it is considered to be text !

 

[bschwartz]

Doh.....sorry, I meant format as Currency.
Whilever, the value in the cell is on the LHand side of the cell, it is considered to be text !

No matter how I format the cells, there is no change. $ symbols do not appear or disappear based on those changes, and Excel does not recognize them as numbers in either case.

 

[Aladin Akyurek]

No matter how I format the cells, there is no change. $ symbols do not appear or disappear based on those changes, and Excel does not recognize them as numbers in either case.

Select all cells.
Activate Replace.
Set Find What to: $
Set Replace With to: $
Activate the Replace All button.

And run the following recipe:

Copy an empty, unused cell.
Activate Edit | Paste Special and run the Add option.

Hopefully they are now allnumbers.

 

[bschwartz]

I believe I can provide more insight into the problem. There are empty spaces in the cells that were copied and pasted. The TRIM function should remove all of those spaces and that will allow the calculations. Proof being when I manually remove the spaces, the cells function properly. However, for some reason, when I use the trim function, it is leaving spaces at the end of the cells.

Example:
in G5: TRIM(E5), copy
in E5: Paste value
Result: 1062.37__ (two blank spaces)

 

[bschwartz]

Select all cells.

...

This does not seem to help. See my post above for the probable cause. (Solution still required)

Thanks for posting.

I believe I can provide more insight into the problem. There are empty spaces in the cells that were copied and pasted. The TRIM function should remove all of those spaces and that will allow the calculations. Proof being when I manually remove the spaces, the cells function properly. However, for some reason, when I use the trim function, it is leaving spaces at the end of the cells.

Example:
in G5: TRIM(E5), copy
in E5: Paste value
Result: 1062.37__ (two blank spaces)

 

[bschwartz]

I have found the solution.

Formula I needed is: =TRIM(SUBSTITUTE(E5,CHAR(160),CHAR(32)))

Helpful Link: Excel TRIM Function Alternative Formula

Thanks everyone for your kind assistance.

 

[Aladin Akyurek]

I have found the solution.

Formula I needed is: =TRIM(SUBSTITUTE(E5,CHAR(160),CHAR(32)))

Helpful Link: Excel TRIM Function Alternative Formula

Thanks everyone for your kind assistance.

Try the TrimAll macro, which you can add to your workbook:

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, <acronym title="visual basic for applications">VBA</acronym> does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub