Extracting from a column

[MiguelS]

IV:9710388235LADD DISTRIBUTION LLC

Did a search on extract, but couldnt find a solution, so I wanted to see if someone could help out.

I want to populate 3 columns from 1 column.

Column 1 would have IV: - simple enough as I can trim 3 characters.

Column 2 would have 9710388235 - Anything after char # 3, but up to the last Numerical DIgit.

Column 3, LADD DISTRIBUTION LLC - Everything after the Numerical value.

 

[Rick Rothstein]

=TRIM(MID(SUBSTITUTE(":"&REPLACE($D1,LOOKUP(1,-MID($D1,ROW($D$1:$D$200),1),ROW($D$1:$D$200))+1,,":"),":",REPT(" ",200)),200*COLUMNS($D:D),200))

That is a nice formula, but it is not perfect as it fails for this Column D value that the OP showed in Message #11...

Py:129255 DIGI-KEY 97789

Just so the OP knows, this formula will fail whenever there are digits embedded in or following the company name. Still, this should cover more than 99% of the OP's needs.

As a follow-up to my comment above, here is a UDF (user defined function) that will work correctly for all of the examples the OP has shown us...

Function SplitData(S As String, Field As Long) As String
  Dim X As Long, StartAt As Long, LastDigit As Long
  Const Months As String = "JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC"
  If Field = 1 Then
    SplitData = Left(S, InStr(S, ":") - 1)
  Else
    StartAt = InStr(S, ":") + 1
    If InStr(Months, Mid(S, StartAt, 3)) Then
      If InStr(Replace(S, " ", ""), "-") < StartAt + 7 Then StartAt = InStr(S, "-")
    End If
    For X = StartAt + 1 To Len(S)
      If Mid(S, X, 2) Like "#[!0-9-]" Then
        LastDigit = X
        Exit For
      End If
    Next
    If Field = 2 Then
      SplitData = Trim(Mid(Left(S, X), InStr(S, ":") + 1))
    ElseIf Field = 3 Then
      SplitData = Trim(Mid(S, X + 1))
    End If
  End If
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SplitData just like it was a built-in Excel function. For example, put this formula in cell E

=SplitData($D1,COLUMNS($E:E))

and copy it across to cell G1, then copy those three cells down to the bottom of your data.

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[AlKey]

Hi Rick,
LOL!!! I did not expect that the formula I suggested would give you so much grief
Best regards,

 

[MiguelS]

That function works great! Thanks.