How to UNCONCATENATE cell values

[UNCONCATENATE function]

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]

Hi

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

HTH
Frank

 

[Peo Sjoblom]

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

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]

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

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]

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.