0

I have a Macro in Excel that is trying to convert text to number (currency) a Column.

The values in the cells are text that looks like this "USD 8,765.43" the objective would be to have a currency value that looks like this "$8.765,43"

So I basically have this macro that removes the blank spaces and then replaces everything to make it look as I need and then change the format of the cell to number.

Columns("H:H").Select

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False

Selection.Replace What:="USD", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

    Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

Selection.NumberFormat = "[$$-409]#,##0.00"

All changes are done all right except that the number appears like this: 8.765,43 but does not either sum with other numbers and is not "currency" UNTIL I enter the cell, and press the "ENTER" key at the end of the number, then it does convert to number, dues sum and appears the "$" sign.

thanks,

2 Answers 2

1

In "USD 8,765.43" there is a blank between USD and the number, which will still be around at the end, and prohibit Excel from recogninzing it as a number.

Change your first replacement to get that removed too:

Selection.Replace What:="USD ", Rep...

5
  • this is done at the beggining , the code has not pasted properly:
    – AlbertoPow
    Dec 14, 2015 at 15:36
  • ok... I think I had a similar issue a long time ago, Excel does not realize that it should reconsider the cell's display now. You could try setting the value of selection to itself, but that might or might not help.
    – Aganju
    Dec 14, 2015 at 15:39
  • Is your target format the right way around? "#,##0.00"? Did you not write you want "#.##0,00" [dot for thousands, comma for decimal?]
    – Aganju
    Dec 14, 2015 at 15:40
  • You arre right Will, it was not correct, but I have changed it and still is not working. the value remains as TEXT until you enter the cell and press ENTER, I have tried to add an sendkey ENTER but it does not work either because you need to enter the cell and then press ENTER.
    – AlbertoPow
    Dec 14, 2015 at 16:22
  • @AlbertoPow selection.value = selection.value should correct that.
    – Kyle
    Dec 14, 2015 at 19:26
1

Try to multiply each cell in your selection by 1 . It forces Excel to recalculate and apply formats:

Dim cell As Range 
For Each cell In Selection 
If Not IsError(cell) Then cell = val(cell) * 1 
Next cell

Put that piece of code just after your

Selection.NumberFormat = "[$$-409]#,##0.00"

5
  • It returns an error Run-time error '13', type mismatch in cell= cell*1
    – AlbertoPow
    Dec 14, 2015 at 19:02
  • 1
    which shows that the cell contains still not a number but a text. Verify if there is another character in the cell - when you click in it, don't press ENTER, but move the cursor to very beginning, and then with <cursor right> to the very end. Watch if there is an extra movement, for example when a TAB character is there or something fancy like that. If so, you need to replace that one too.
    – Aganju
    Dec 14, 2015 at 19:09
  • also try without the "[$$-409]" - you should not need that. It is a locale code, given in hexadecimal. It determines what is shown when you use the various date time format codes - which you don't.
    – Aganju
    Dec 14, 2015 at 19:12
  • @albertoPow try now, with the val() function. It converts and should take care of any blank or space.
    – P. O.
    Dec 14, 2015 at 20:30
  • Thanks P.obertelli, your code worked great! it does the conversion from text to number perfectly now
    – AlbertoPow
    Dec 15, 2015 at 19:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .