Link to home
Start Free TrialLog in
Avatar of Tricky
TrickyFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of cri
cri
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ?
Avatar of Tricky

ASKER

Perfect, nice job :)

Thanks...
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.
Avatar of Tricky

ASKER

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

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