How to UNCONCATENATE cell values


G

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

Advertisements

F

Frank Kabel

Hi

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

HTH
Frank
 
P

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


UNCONCATENATE function said:
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.
 
J

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


UNCONCATENATE function said:
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.
 
K

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.
 
Joined
Sep 4, 2013
Messages
1
Reaction score
0
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
 
Ad

Advertisements

Joined
Nov 3, 2015
Messages
1
Reaction score
0
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
 

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. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unconcatenate 12

Top