Extracting data from a Cell using Macro/VB

funkey_monkey · 23-04-2015 8:36pm #1

Hi,

I need some help writing a macro to manipulate some data.

In a cell I have of the following format:

VariableLengthText1::VariableLengthText2::VariableLengthText3:: ... VariableLengthTextN::Name1.Name2

In Excel I can easily extract Name2 from the string by using the following formula:

= RIGHT(B2, LEN(B2) - FIND(".", B2))

However, I'm struggling to extract Name1 in a succinct manner. I can perform it across a number of cells but I would like to create a Macro/VB script which would perform the task in a script that I could run by using a keyboard shortcut.

My "vision" for the script is that I click on cell B2 to activate it and the script will then extract Name1 and place into cell C2 and Name2 into cell D2.
The script would then progress downwards to B3 and continue until it reached a null cell in column B.

Is this possible and how can it be done? I been given a task at work and it is taking me ages to do this manually across the numerous input files I have to process.

Unfortunately, I've little or no experience of recording marcos so I don't know how to do this.

I would appreciate any help with this as I'm struggling to meet my deadline. Training course beckons...

Thanks.

breadmonkey · 23-04-2015 9:04pm

EDIT: Was close but thinking about it there's a flaw in the formula. Will have a look again tomorrow if you don't get any more replies.

funkey_monkey · 23-04-2015 10:54pm

Thanks - that would be greatly appreciated.

I've got this task to perform across numerous sheets in a workbook and for big amounts (rows) of data. Like I say I could do it via formulas which search for "::", then in an adjacent cell add +1 to the position returned and do this for approx 6 - 8 times. Taking the max of these results would then give me the final "::" location.

It works, but depends on Name1.Name2 not being nested more than 6 - 8 layers deep.

Note this was done in OO Calc:

Data is in A5...

In B2 type
= FIND("::"; A$5)

In C2 type
=IF(ISERR(FIND("::";$A$5;(B$5+1)));B$5;FIND("::";$A$5;(B$5+1)))

Drag this across to column H*

In I5 type
=MAX($B$5:$H$5)

In J5 type
=LEFT(RIGHT($A$5;(LEN($A$5)-$I$5-1)); LEN($K$5)-1)

In K5 type
=RIGHT($A$5;(LEN($A$5)-FIND(".";$A$5)))

This will return Name1 in cell J5 and Name2 in cell K5.

However, I find this to be overly complex and would like it simplified into a simple VB script. It would also help me to learn VB.

All this because MS Excel does not facilitate searching from right to left in a cell...

* Assuming that extending to Column H is sufficient. Personally I hate this type of assumption and would prefer recursion until all conditions have been satisfied - increased robustness.

breadmonkey · 23-04-2015 11:32pm

If I've understood the structure of your text string correctly then this function will do what you want:

Function getName(txt As String, choice As Integer) As String
'   Txt is this thing: VariableLengthText1::VariableLengthText2::VariableLengthText3::VariableLengthTextN::Name1.Name2
'   choice is 1 to return name1 or 2 to return name2

    Dim NameArr1()              As String
    Dim NameArr2()              As String
    
    NameArr1 = Split(txt, "::")
    NameArr2 = Split(NameArr1(UBound(NameArr1)), ".")
    
    If choice = 1 Then
        getName = NameArr2(LBound(NameArr2))
    ElseIf choice = 2 Then
        getName = NameArr2(UBound(NameArr2))
    Else
        getName = "Err"
    End If
    
End Function

So if the string to be processed is in cell A1, you would just use the formula...

=getName(A1, 1) if you wanted Name1

or

=getName(A1, 2) if you wanted Name2

No sure how familiar you are with VBA in Excel but you need to go into the VB editor, insert a new module and paste the code above into it.

funkey_monkey · 24-04-2015 8:56am

Thanks - not really familiar with VB, but I need both names taken out into adjacent cells.

How do I make it loop until the source cell is blank? I cna't see how this works on activation from Excel - i.e. I can't see where the cell reading and writing comes in.

Thanks.

breadmonkey · 24-04-2015 9:18am

I think you have in your mind that you want do this by clicking a button and having excel populate the workbook with all of the names. That is certainly possible but unnessecarily complicated.

What I've given you above is a custom function to extract ether Name1 or Name2 from your text string. Once it's loaded into a module in the VB editor you can use it just like any other excel function (sum, count, left, right, vlookup etc...).

Try this link to see how to get it working: h[URL="I think you have in your mind that you want do this by clicking a button and having excel populate the workbook with all of the names. That is certainly possible but unnessecarily complicated.

What I've given you above is a custom function to extract ether Name1 or Name2 from your text string. Once it's loaded into a module in the VB editor you can use it just like any other excel function (sum, count, left, right, vlookup etc...).

Try this link to see how to get it working: http://www.rondebruin.nl/win/code.htm

funkey_monkey · 24-04-2015 10:37am

Thanks – sorry I didn’t appreciate that we could extend the function library ourselves. I thought only macros could be created to be executed. This is a really neat solution to my problem – thanks.

Extracting data from a Cell using Macro/VB

Comments