How to UNCONCATENATE cell values

[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.

 

[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.

 

[rooboy1999]

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

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

 

[Mac123]

I wanna ask this quest. for security reason and just saying that I concatenate the formulas as
=code(55)&find("a",A1,3)&char(C35)
on the other hand if I have no.

776567495051

I want it to convert as char and get result as
MAC123
Is this possible or not.
So I wanna unconcatenate No. to 77,65, 67,49,50 and 51.

Suppose if formula is " Unconcatenate" so

=char(unconctenate(776567495051))
Result
MAC123