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
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Bvclsaaal

    Compare 4 columns & find dissimilar

    Bvclsaaal, Jul 2, 2011, in forum: Excel
    Replies:
    1
    Views:
    598
    XLPadawan
    Jun 15, 2016
  2. Nago
    Replies:
    0
    Views:
    523
  3. baldi
    Replies:
    1
    Views:
    776
    Sohail
    Dec 25, 2014
  4. marste7

    Copy and Paste Formula

    marste7, Aug 27, 2013, in forum: Excel
    Replies:
    1
    Views:
    704
    XLPadawan
    Jun 16, 2016
  5. asyluma
    Replies:
    0
    Views:
    459
    asyluma
    Oct 9, 2013
  6. teza2k06

    Conditional Formatting Formula

    teza2k06, Aug 23, 2014, in forum: Excel
    Replies:
    2
    Views:
    575
    XLPadawan
    Jun 16, 2016
  7. DSnuh
    Replies:
    2
    Views:
    380
    OfficeNewb
    Nov 10, 2015
  8. iggydarsa
    Replies:
    0
    Views:
    261
    iggydarsa
    Apr 27, 2015
Loading...