Want to split alpha numerals and then compare and provide difference using macro

[aravindkm]

Hi excel gurus,

We have process were we receive 2 set of datas one just numerals and another one the same data but with alpha numerals we want to split the alpha numerals and compare the balances.

The columns will be like below

Currency. deal. balance
THB THB113456 100000

Kindly help us with Macro to find the difference

[alansidman]

Are the alphas always three characters in length?

[adhawan06]

Hi Aravind,

Use following functions to separate your text and numbers from a cell.

To separate text - "=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)"
To separate number - "=RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)"

Place your formula anywhere but the value should be in A2 to test this function.

Let me know if it works for you.

[aravindkm]

Is it possible to do it through a macro

[adhawan06]

Is it possible to do it through a macro

Hi Aravind,

Yups, there is always a way to do anything in this world. However, I m not good in Macros and dont want to give you any bad example. :P

Our other experts may help you in this.

[MarvinP]

Hi Aravind,

See if this code in a User Defined Function (UDF) does what you need.

Function DiffWithAlpha(strInput As String)
    Dim Parts As Variant
    
    Parts = Split(strInput, " ")
    On Error Resume Next
        Do Until CDbl(Parts(1)) > 0
            Parts(1) = Mid(Parts(1), 2)
        Loop
    On Error GoTo 0
    
    DiffWithAlpha = Parts(1) - Parts(2)
End Function

[aravindkm]

Thanks for your code

However I have 2 worksheets where one sheet has the data without alpha numerals and other sheet has data with alpha numerals with the same balances I want to have split the alpha numerals in the second sheet and get the difference in the balances if any between 2 sheets

[sktneer]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[aravindkm]

Hi,

Please find enclosed worksheet with macro for your reference.

Where the Macro is working fine however when the deals were in two different rows the difference is coming wrongly.

Kindly help in fixing the macro to help me save time.

Thanks

Aravind

[sktneer]

Try this to see if you get the correct output....

Sub Test()
Dim lr As Long
Dim rng As Range
lr = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("E2:E" & lr)
rng.Formula = "=VLOOKUP(""*""&C2&""*"",BB!$C$2:$D$100,2,0)-D2"
End Sub

[aravindkm]

Hi,

Thanks for the code.

Since the row is too high use more than 1 lakh rows this macro is taking too much time.
Could you please help me some other way.

Please

Thanks

[smr]

Hi,
This formula works fine if the cells are in a format such as BA123. Can you please help me with a formula that will do the same for my data? See Example:
iNITIAL Data Numeric Results Needed Alpha Results Needed
79 79
79A 79 A
234 234
234A 234 A
234B 234 B
234C 234 C

Thank You
Sharon