asked on

Creating an advanced Excel macro

Hi,

I have a database holding about 90.000 lines, which I am constructing in Excel and importing into MuSQL frem there.

In this database I have a column that holds three names.

What I need is a macro that grabs the last of those three words and copy it to the cell one column to the right (which is empty)

For example I have this in the two columns:

And what I need is this:

My question is, if it is possible to make a macro that does this in selected areas?

Subodh Tiwari (Neeraj)

Which column contains "Bornholm Vester Knudsker"? You said "I have this in two columns", do you mean the cells are merged?

Subodh Tiwari (Neeraj)

Paste the following code on a Standard Module like Module1.

Function GetLastWord(ByVal vStr As String) As String
Dim str() As String
str() = Split(VBA.Trim(vStr), " ")
GetLastWord = str(UBound(str))
End Function

Open in new window

And then use this user defined function on the sheet like below...
Assuming your string is in A2, then try

=GetLastWord(A2)

Open in new window

Peter Kroman

ASKER

No merged cells :)
Bornholm Vester Knudsker is in one cell and the cell to its right is empty.

How do I activate the function? It does not appear as a macro in the macro list ...

Peter Kroman

ASKER

I have made a macro like this:
Sub extractLastWord()
GetLastWord (C)
End Sub

When I run this macro I get this error

saying "The index is outside the area"

Peter Kroman

ASKER

... and it stops here:

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

Peter Kroman

ASKER

Thanks Subodh,

I believe I have payed attention to what you have suggested, and I also believe that the code you have suggested now is rather different from the first code you sent. I will try it, and work with it, and I will get back to you if needed.

Thanks for your help.

Peter Kroman

ASKER

Tahnks to Subodh.

The macro works beautifully now with this code:
Sub GetLastword()
Dim lr As Long, i As Long
Dim x, str() As String

lr = Cells(Rows.Count, "B").End(xlUp).Row
x = Range("B2:C" & lr).Value
For i = 1 To UBound(x, 1)
str = Split(x(i, 1), " ")
x(i, 1) = str(UBound(str))
Next i

Range("C2").Resize(UBound(x, 1)).Value = x
End Sub

Subodh Tiwari (Neeraj)

You're welcome Peter! Glad it worked for you.
The code I suggested earlier was a User Defined Function and it is either used on the sheet as a regular excel formula or inside a sub routine (macro). You tried to use it in a wrong way. That's why I suggested a sub routine (macro) which you can run to get the desired output.