How to edit excel value, example: I have value "12345 (POS) / 45678 (APOS) " in excel cell-CodePudding

I have value "12345 (POS) / 45678 (APOS) " in excel cell and i want to extract the value in this format "12345,45678" using excel operation

CodePudding user response：

=SUBSTITUTE(SUBSTITUTE(A2," (POS) / ",",")," (APOS)","")

CodePudding user response：

If you can use VBA.

If we also assume that each time you have two values (with string) separated by a "/". The code is as follows:

Sub Transform_Into_Num_Value()

    Dim myVal
    myVal = "12345 (POS) / 45678 (APOS)" ' Replave by your cells that contains the value to be tranformed.
    myVal = Split(myVal, "/")
    myVal = Val(myVal(0)) & "," & Val(myVal(1))
    ' You can also use myVal = myVal = Join(myVal, ",") instead this concatenation       ' MsgBox myVal 'Decomment if you have to verify a single value 
End Sub