How to UNCONCATENATE cell values

Discussion in 'Microsoft Excel Worksheet Functions' started by Guest, Jan 20, 2004.

  1. Guest

    Guest Guest

    I know how use CONCATENATE function, but I would like to know is there a reverse of this function, without havingb to export to text file, and then import data back into Excel. For instance, If I have cell value of "10.20.14.256", how can I extrapolate values separated by ".". into separate cells.
     
    Guest, Jan 20, 2004
    #1
    1. Advertisements

  2. Guest

    Frank Kabel Guest

    Hi

    use 'Data - Text to Column' and choose the dot as seperator

    HTH
    Frank

    UNCONCATENATE function wrote:
    > I know how use CONCATENATE function, but I would like to know is
    > there a reverse of this function, without havingb to export to text
    > file, and then import data back into Excel. For instance, If I have
    > cell value of "10.20.14.256", how can I extrapolate values separated
    > by ".". into separate cells.
     
    Frank Kabel, Jan 20, 2004
    #2
    1. Advertisements

  3. Guest

    Peo Sjoblom Guest

    Data>text to columns, delimited,click next, delimiter check other and put a
    period, click finish
    Note that the next columns to the right must be empty or they will be
    overwritten (in your example the 3 next columns to the right), if not insert
    new columns to cover that

    --

    Regards,

    Peo Sjoblom


    "UNCONCATENATE function" <> wrote in
    message news:...
    > I know how use CONCATENATE function, but I would like to know is there a

    reverse of this function, without havingb to export to text file, and then
    import data back into Excel. For instance, If I have cell value of
    "10.20.14.256", how can I extrapolate values separated by ".". into
    separate cells.
     
    Peo Sjoblom, Jan 20, 2004
    #3
  4. Guest

    John Wilson Guest

    Dear anonymous,

    It's always nice to have at least a first name to reference when replying
    to a post (John, Harry, Mary, Elisha, anything will do).

    There are a few ways to accomplish what you want.
    One of the easier solutions would be to use j-walk's
    ExtractElement function.
    Place the following code in a regular module:

    Function ExtractElement(str, N, sepChar)
    ' Returns the nth element from a string,
    ' using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If N > 0 And N - 1 <= UBound(x) Then
    ExtractElement = x(N - 1)
    Else
    ExtractElement = ""
    End If
    End Function

    With your example data (10.20.14.256) in A1.
    In any cell:
    =ExtractElement(A1,1,".")
    will get the first element before the first period.
    =ExtractElement(A1,2,".")
    will get the string between the first and second period.
    etc., etc.

    John


    "UNCONCATENATE function" <> wrote in
    message news:...
    > I know how use CONCATENATE function, but I would like to know is there a

    reverse of this function, without havingb to export to text file, and then
    import data back into Excel. For instance, If I have cell value of
    "10.20.14.256", how can I extrapolate values separated by ".". into
    separate cells.
     
    John Wilson, Jan 20, 2004
    #4
  5. Guest

    Kevin M Guest

    Hi Anonymous,

    You need to use "Text to columns" under the Data heading.
    The wizard will help you to figure out what character(s)
    it will look for when splitting cells. You want to make
    sure you choose "delimited". Fixed width will seperate
    the cells by a column width you assign.
    HTH
    Kevin M
    >-----Original Message-----
    >I know how use CONCATENATE function, but I would like to

    know is there a reverse of this function, without havingb
    to export to text file, and then import data back into
    Excel. For instance, If I have cell value
    of "10.20.14.256", how can I extrapolate values
    separated by ".". into separate cells.
    >.
    >
     
    Kevin M, Jan 20, 2004
    #5
  6. Guest

    rooboy1999

    Joined:
    Sep 4, 2013
    Messages:
    1
    Likes Received:
    0
    Thanks Kevin M, this was just what I needed! Was looking for an Excel formula to do the same thing, over complicating it! Cheers.

    Peter H
     
    rooboy1999, Sep 4, 2013
    #6
    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. Guest

    vlookup, multiple values, sum values into one cell??

    Guest, Nov 8, 2005, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    2,774
    Guest
    Nov 9, 2005
  2. Guest

    Unconcatenate

    Guest, Nov 20, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    12
    Views:
    1,481
  3. Joao Lopes

    How to assign values to a cell based on values in another cell?

    Joao Lopes, Dec 5, 2007, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    200
    Gord Dibben
    Dec 5, 2007
  4. Populate a cell if values in cell 1 and cell 2 match cell 3 and 4

    , Aug 21, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    364
  5. Claus Busch

    Re: Get sum of values in a cell based on unique values in another cell

    Claus Busch, Aug 20, 2012, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    318
    Claus Busch
    Aug 20, 2012
Loading...

Share This Page