Convert Formula to VBA

[mintz]

=IFERROR(IFERROR(VLOOKUP(RIGHT(A1,LEN(A1)-FIND(",",A1)-1),2,FALSE),RIGHT(A1,LEN(A1)-FIND(",",A1)-1)),A1))

The formula takes the State from a City&State field (i.e. New York, NY) or in case there are multiple commas then it will take everything before the last comma i.e. Luganville, Santos, PR >> PR) or in case there are no commas it will keep the whole value in tact

How do I convert this formula to VBA?

 

[CsJHUN]

Hi Mintz!

The easeest way to write this into the desired cell, hit enter. Start macro recorder -> click back to the cell (edit mode) -> hit enter again
The recorder will record smg like this:

sub mintzmacro()
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(IFERROR(VLOOKUP(RIGHT(RC[-2],LEN(RC[-2])-FIND("","",RC[-2])-1),2,FALSE),RIGHT(RC[-2],LEN(RC[-2])-FIND("","",RC[-2])-1)),RC[-2])"
end sub

 

[tygrrboi]

Try this

Trim(Right(Replace([A1], ",", String(1000, " ")), 1000))

or

Dim r as Range
Set r = ActiveSheet.Range("A1")

Trim(Right(Replace(R, ",", String(1000, " ")), 1000))

 

[mintz]

Try this

Trim(Right(Replace([A1], ",", String(1000, " ")), 1000))

or

Dim r as Range
Set r = ActiveSheet.Range("A1")

Trim(Right(Replace(R, ",", String(1000, " ")), 1000))

That worked great! How do I get the left part? (everything before the last comma)
I tried Left instead of Right, it works when there's one comma like New York, NY
but if there's something like Queens, New York, NY it just takes Queens

 

[GTO]

Greetings mintz,

As a UDF maybe:

Option Explicit
  
Public Function SplitLastDelimiter(ByVal Cell As Range, ByVal Delimiter As String) As Variant()
Dim LeftSide As Variant
Dim RightSide As Variant


  If InStr(1, Cell.Value, Delimiter) Then
    LeftSide = Trim(Left(Cell.Value, InStrRev(Cell.Value, Delimiter) - 1))
    RightSide = Trim(Mid(Cell.Value, InStrRev(Cell.Value, Delimiter) + 1))
  Else
    If Cell.Value = vbNullString Then
      LeftSide = vbNullString
    Else
      LeftSide = Cell.Value
    End If
    RightSide = vbNullString
  End If
  
  SplitLastDelimiter = Array(LeftSide, RightSide)
  
End Function

Where the formula would be array-entered into adjoining cells:

Excel Workbook
	A	B	C
2	My House, Phoenix, AZ	My House, Phoenix	AZ
3	Phoenix, AZ	Phoenix	AZ
4	My House in Phoenix AZ	My House in Phoenix AZ
Sheet3

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Does that help?

Mark

 

[mintz]

Greetings mintz,

Does that help?

Mark

Thanks a lot mark, worked great for me!

Here's a different solution I managed to come up with
sState = Right([A1], Len([A1]) - InStrRev([A1], ","))sCity = Left([A1], InStrRev([A1], ",") - 1)

 

[Rick Rothstein]

=IFERROR(IFERROR(VLOOKUP(RIGHT(A1,LEN(A1)-FIND(",",A1)-1),2,FALSE),RIGHT(A1,LEN(A1)-FIND(",",A1)-1)),A1))

The formula takes the State from a City&State field (i.e. New York, NY) or in case there are multiple commas then it will take everything before the last comma i.e. Luganville, Santos, PR >> PR) or in case there are no commas it will keep the whole value in tact

How do I convert this formula to VBA?

Is your state always two letters long as shown? If so...

strState = Right(Range("A1").Value, 2)

 

[GTO]

Thanks a lot mark, worked great for me!

Here's a different solution I managed to come up with

sState = Right([A1], Len([A1]) - InStrRev([A1], ","))
sCity = Left([A1], InStrRev([A1], ",") - 1)

Hi mintz,

You are very welcome. In regards to your alternate solution, while I would tend to avoid unnecessary use of Evaluate and particularly the shorthand form, I would suggest that regardless of methodology, you will want to include some type of test to ensure that the delimiter exists.

Mark

 

[navic]

The formula takes the State from a City&State field (i.e. New York, NY)

Another solution without VBA.
Based on example source data by @GTO

Try in B2 and copy down

=IF(ISNUMBER(FIND(",",RIGHT(MID(A2,1,LEN(A2)-3),1),1)),MID(A2,1,LEN(A2)-3-1),MID(A2,1,LEN(A2)-3))

Try in C2 and copy down

=IFERROR(MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,",",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),",",""))))+2,10),RIGHT(A2,2))