A Formula that will do Text to Columns

Discussion in 'Excel' started by CarolB, Apr 24, 2012.

  1. CarolB

    CarolB

    Joined:
    Apr 17, 2012
    Messages:
    3
    Likes Received:
    0
    I have a series of numbers in a cell. I need to be able to pull one section out of it as part of a formula because the people using the Excel spreadsheet do not know Excel well enough to be expected to do Text to Columns for themselves.

    For example, 1.1234.12345.1234 and I would need to be able to pull out the 12345 to be able to vlookup to a table with descriptions.

    Does anyone know if this is possible?
     
    CarolB, Apr 24, 2012
    #1
    1. Advertisements

  2. CarolB

    dallton

    Joined:
    Dec 31, 2012
    Messages:
    3
    Likes Received:
    0
    Location:
    Ohio
    I created this to allow me to convert several columns at once. You may need to make some changesw to get exactly what you need.

    Public Sub MultColTxtToCol()
    'This routine runs the Text To Column function on all selected columns
    Dim intCol As Integer, intx As Integer, intCurrentCol As Integer
    intCurrentCol = ActiveCell.Column
    intCol = Selection.Columns.Count

    For intx = intCurrentCol To intCurrentCol + intCol - 1
    Columns(intx).Select
    Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
    Next
    MyEndMessage
    End Sub
     
    dallton, Dec 31, 2012
    #2
    1. Advertisements

  3. CarolB

    XLPadawan

    Joined:
    Jun 9, 2016
    Messages:
    32
    Likes Received:
    5
    Location:
    Allen, TX
    How would we know which section to pull out for the VLOOKUP?
    1.1234.12345.1234 has four sections. Will there always be four sections? If not, what is the maximum number of sections? Will it always be the same section, e.g. will the third section always be the one used for the VLOOKUP?
     
    Last edited: Jun 15, 2016
    XLPadawan, Jun 15, 2016
    #3
  4. CarolB

    Luck

    Joined:
    Dec 28, 2017
    Messages:
    8
    Likes Received:
    0
    Try
    =MID(A2, FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),2))+1, FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),3)) - FIND(CHAR(1),SUBSTITUTE(A2,".",CHAR(1),2))-1)
     
    Luck, Dec 28, 2017
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.