• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6247
  • Last Modified:

The opposite of concatenate in Excel ?

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
Tricky
Asked:
Tricky
  • 4
  • 2
1 Solution
 
criCommented:
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
 
criCommented:
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
 
TrickyAuthor Commented:
Perfect, nice job :)

Thanks...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
criCommented:
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
 
TrickyAuthor Commented:
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
 
criCommented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now