asked on

VBA: retain first and second level based on "."

Hello experts,

I have a column composed with the following structure

Bu.Division.Lob.sublob1.sulob2
Bu.Division.Lob.sublob1
Bu.Division.Lob.sublob1.sublob2.sublob3

I am looking for a procedure in order to report exclusively BU.Division in the Column B.
The values reported as Division are not the same as result I can not play with the len string and I should find a way to retain the string part which have two "." "."

I supposed that I need to find a way with insert a condition like this:

If Len(cell.Value) - Len(Replace(cell.Value, ".", vbNullString)) >=2

Here is my initial proposal:

Sub retainlevel1level2()

    Dim rng As Range, cell As Range
    Dim lrow As Long

    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row

    Set rng = Range("A1:A" & lrow)

    For Each cell In rng

'....................................... 'Condition to add

    Next cell

End Sub

Open in new window

Thank you again for your help.

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

Or try

        aCell = Split(cell, ".")
        cell.Offset(, 1) = aCell(0) & "." & aCell(1)

Open in new window

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you very much for those proposals.
I tested and they works

Just one question if I want to customize the values of cell.offset (0,1) in order to have upper case of the first letter of
arr(0), arr(1) and arr(2) and lower case for the rest of the letter how should I modify the proposals?

Thank you again for your help.

SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Thank you very much for your help.

Regards,

VBA: retain first and second level based on &quot;.&quot;

VBA: retain first and second level based on "."