Swap two values in a cell?

[Jyggalag]

Hi all,

I currently have these values in a larger format from rows J - N. I have made a small example of my data here:

I essentially want to swap my "." with my "," in every cell, if applicable.

If not applicable, I want to replace my "." with a "," and my "." with a ","

Does this make sense, is it possible?

Thank you!

 

[Jyggalag]

UPDATE:

I have found this in "currency" that could solve my problem. However, the format "1,234.10" is not available. any idea why?

 

[Jyggalag]

UPDATE:

I have found this in "currency" that could solve my problem. However, the format "1,234.10" is not available. any idea why?

View attachment 72457

Edit* It does not work anyway. I just tried to apply the "1.234,10" format to a cell in which I had the value "2,007.30" and it remains at this value and does not change...

 

[bebo021999]

Is it Regional Setting in Control Panes? Have you tried it?

 

[Jyggalag]

Is it Regional Setting in Control Panes? Have you tried it?

I tried this:

It does not work.

I do not think regional settings will help either.

Right now it seems that excel does not even recognize 2,007.30 as a value at all Even if i change the formatting "General" to "Number"!

Very strange indeed. Would truly appreciate any solution that can solve this without me manually editing 5000 rows haha

 

[bebo021999]

Try Find & Replace tool to convert text into number:
Select the range.
Ctrl-H

1) First to remove thousand delimiter:
Find what: ","
Replace with: <input nothing here>

2) Then replace "." with ","
Find what: "."
Replace with: ","

 

[Jimmypop]

Good day

As per bebo021999 suggestion try the below:

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

Just select your range and run the code...

 

[Jyggalag]

Try Find & Replace tool to convert text into number:
Select the range.
Ctrl-H

1) First to remove thousand delimiter:
Find what: ","
Replace with: <input nothing here>

2) Then replace "." with ","
Find what: "."
Replace with: ","

This worked. Can't believe I did not think of this haha :D

I just inserted a random value for "," and then I replaced all . with , and after this i replaced the random value with a .

Amazing. Thanks!

 

[Jyggalag]

Good day

As per bebo021999 suggestion try the below:

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

Just select your range and run the code...

I also really like this idea, although I'm not sure how to implement it! Is this a VBA code that I attach to a macro? And if so, how would I edit it if I only wanted it to make the replacements in column J-N?

 

[Jimmypop]

I also really like this idea, although I'm not sure how to implement it! Is this a VBA code that I attach to a macro? And if so, how would I edit it if I only wanted it to make the replacements in column J-N?

Hi There...

Yes... so steps to follow will be...

1. Hit Alt+F11 to open the VBA Editor...
2. Insert a module
3. Paste the code below into the window

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

4. Close the VBA Editor

On your worksheet you can then Alt+F8 to run the macro or you can assign a button to it.

To assign a button you would need to have the Developer Tab Open. I assigned my test to a button but Alt+F8 will also work.