PC Review


Reply
Thread Tools Rate Thread

How to UNCONCATENATE cell values

 
 
=?Utf-8?B?VU5DT05DQVRFTkFURSBmdW5jdGlvbg==?=
Guest
Posts: n/a
 
      20th Jan 2004
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.
 
Reply With Quote
 
 
 
 
Frank Kabel
Guest
Posts: n/a
 
      20th Jan 2004
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.



 
Reply With Quote
 
 
 
 
Peo Sjoblom
Guest
Posts: n/a
 
      20th Jan 2004
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" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
John Wilson
Guest
Posts: n/a
 
      20th Jan 2004
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" <(E-Mail Removed)> wrote in
message news:(E-Mail Removed)...
> 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.


 
Reply With Quote
 
Kevin M
Guest
Posts: n/a
 
      20th Jan 2004
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.
>.
>

 
Reply With Quote
 
New Member
Join Date: Sep 2013
Posts: 1
 
      4th Sep 2013
Quote:
Originally Posted by Kevin M View Post
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
unconcatenate a strinp =?Utf-8?B?SGFnZ3I=?= Microsoft Access VBA Modules 1 7th Feb 2007 07:44 AM
Unconcatenate =?Utf-8?B?UXVsbDY2NkBob3RtYWlsLmNvbQ==?= Microsoft Excel Worksheet Functions 12 21st Nov 2006 06:51 AM
unconcatenate =?Utf-8?B?UXVsbDY2NkBob3RtYWlsLmNvbQ==?= Microsoft Excel Misc 2 20th Nov 2006 10:44 AM
Unconcatenate and Replace Text crystalgatewood Microsoft Excel Programming 7 19th Apr 2006 07:16 AM
unconcatenate? Patty via OfficeKB.com Microsoft Excel Misc 8 12th Sep 2005 07:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 08:29 PM.