Solved

The opposite of concatenate in Excel ?

Posted on 2001-06-26
6
6,153 Views
Last Modified: 2008-02-26
Hi,

I'm trying to separate a string of text such as: UKFRABC012345-123Z 456000 Left

Which is unfortunately in one cell only, to the following (column separator is ^): UKFR ^ ABC012345 ^ 123Z ^ 456000 ^ left.

Quite straightforward, I need the opposite of concatenate - maybe a nested IF will work, I'm not sure.

Any ideas welcome, 200 up for grabs :)

Thank you,

Tricky
0
Comment
Question by:Tricky
  • 4
  • 2
6 Comments
 
LVL 13

Accepted Solution

by:
cri earned 200 total points
ID: 6227135
Are the positions fixed ? And is using auxiliary cells an option ? If yes to both, use the MID function:
=MID(A1,1,5)=UKFR
=MID(A1,5,9)=ABC012345
etc.

 
0
 
LVL 13

Expert Comment

by:cri
ID: 6227138
Of course you can also make a VBA function which uses the MID function.

If the position is not given, do the separation follow some rules / markers ?
0
 

Author Comment

by:Tricky
ID: 6227160
Perfect, nice job :)

Thanks...
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 13

Expert Comment

by:cri
ID: 6229000
ThAnk you. If you need further info regarding string handling in VBA, please ask, this was a lot of points for this kind of question.
0
 

Author Comment

by:Tricky
ID: 6229478
I needed an accurate answer quickly, so the points were worth it...

I'll be sure to look out for you in the future ;p

0
 
LVL 13

Expert Comment

by:cri
ID: 6230278
Ok.

For the KPro users and PAQ 'buyers':

Lots of usefull tips, including string handling
http://www.cpearson.com/excel/topic.htm

Extracting the n-th Element From a String
http://www.j-walk.com/ss/excel/tips/tip32.htm

Hints/Caveats:

a) Whereas text comparision in worksheets are case insensitive, the default in VBA is case sensitive, unless you start a module with
'Option Compare Text'

b) Excel|Help|Find 'About text functions' will give you a good overview of all regular Excel text functions
 
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now