Extract from a String

[RLJ]

I am looking for some formulas/Macros to work with 2 different strings. I need to extract the number characters into 3 separate cells from within these 2 strings.

Lockout/26_Defeasance/30_0%/4
YM1%/26_YM1%orDefeasance/90_0%/4

The similarity between these 2 types of strings is the fact that the number characters always follow the / character.

So for the First String say in cell A2 would extract 26, B2 30, C2 4
For the Second String A2 26, B2 90, C2 4

 

[Rick Rothstein]

This macro should do what you asked for...

Sub SplitThreeNumbersOut()
  Dim X As Long, Z As Long, LastDataRow As Long, Parts() As String
  Const DataStartRow As Long = 2
  LastDataRow = Cells(Rows.Count, "A").End(xlUp).Row
  For X = DataStartRow To LastDataRow
    Parts = Split(Cells(X, "A").Value, "/")
    For Z = 1 To UBound(Parts)
      Cells(X, Z + 1).Value = Val(Parts(Z))
    Next
  Next
End Sub

 

[RLJ]

Perfect!
Now, I am familiar with code, but this one is stumping me a bit.

The Range with the aplicable strings is AS6 & down to the last applicable row, where I would use Column A to determine the last row.

The Code needs to extract to the Columns AU, AX & BD on my spreadsheet.

Thanks for the code, now to tweak it a bit.

 

[Rick Rothstein]

The Range with the aplicable strings is AS6 & down to the last applicable row, where I would use Column A to determine the last row.

The Code needs to extract to the Columns AU, AX & BD on my spreadsheet.

You really should not simplify your questions when asking them on forums... all that tends to do is get you working answers to questions that you really do not care about. By the way, why would you use Column A to determine where the last row is? If you have data in Column AS, then I would think you would want to procress only those cells.

The Code needs to extract to the Columns AU, AX & BD on my spreadsheet.

For what you have now posted, I think this macro should do what you want...

Sub SplitThreeNumbersOut()
  Dim X As Long, Z As Long, LastDataRow As Long, Parts() As String
  Const DataStartRow As Long = 6
  LastDataRow = Cells(Rows.Count, "AS").End(xlUp).Row
  For X = DataStartRow To LastDataRow
    Parts = Split(Cells(X, "AS").Value, "/")
    For Z = 1 To UBound(Parts)
      Cells(X, Split("AU AX BD")(Z - 1)).Value = Val(Parts(Z))
    Next
  Next
End Sub

 

[RLJ]

Rick, Sorry about the secondary question. Ususlly I am able to take code given and make it fit my situation like this, but I had dificulty with the code figuring out how it was extracted and input to the individual cells. I use

    Dim End_Row As Long
    End_Row = Range("A" & Rows.Count).End(xlUp).Row
[\code]
 
for other macros I have written because that is where my list of loan numbers resides. I may not have this string to exctract from for every row, so my other macros are based off of column a to tell it when to stop running when it gets to the last row.

 

[Rick Rothstein]

I'm not sure from your message whether the code I posted is working for you or not (I think it should be). As for where to determine the last data row, this line of code controls that...

LastDataRow = Cells(Rows.Count, "AS").End(xlUp).Row

If you want to use Column A (I don't think you need to here because you said the data you are parsing is in Column AS), just change the "AS" to "A".

 

[RLJ]

Just had a chance to try it in the actual setting. It works.