Solved

Need formula to parse ~ at varied points

Posted on 2013-12-16
9
166 Views
Last Modified: 2014-02-28
i have single cell data that actually contains 4 fields, seperated with a '~' that I'd like to break out with a formula... it looks like:

aaa~bbbb~cc~dddddd


a,b,c and d are NOT fixed length.
0
Comment
Question by:Tom F
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39721917
Have you tried

Data > Text-to-columns
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 39722453
This will do it, but it's ugly as hell.
EE---split-text-string.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 39722469
Basically, it uses a series of FIND statements to track the position of each Tilde (~).
 - see the attached sheet in the comment above.

Here's how it works.  FIND works like this:

=FIND(CellRef,"~",StartPlaceInThatTextString)

Once it's found one, it uses the position of THAT one + 1, to be the start place for the next Find.

The awkward bit is calculating the Length of characters it should search for.  But this can be done by finding the position of the 2nd ~ and subtracting the position of the 1st one.  You need a minor bit of offsetting, as it's not actually the ~ you want, but what's in-between.  

For the 3rd and 4th ones, it's actually easier to use the totalled Lengths of the previous results in this formula.  

as I said... Ugly as Hell.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:Danny Child
ID: 39722510
minor correction to last part - it worked, but was using a helper cell
Formula in B11 should read:
=RIGHT(B3,LEN(B3)-FIND("~",B3,LEN(B6)+LEN(B8)+3))

Updated copy attached!
EE---split-text-string.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39723903
Or just use Text to columns as suggested by ssaqibh.

In the Text to Columns window, select the delimited option and then in that screen there are a number of options for what to use as a separator. Uncheck all options except Other and in the box next to Other type the ~

Then click OK buttons through to Finish.

Thanks
Rob H
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39723952
If you must have a formula then

If your string is in A2 then you can enter this in B2 and copy it to the right as far as required

=MID("~"&$A2,FIND("#",SUBSTITUTE("~"&$A2,"~","#",COLUMN()-1))+1,FIND("#",SUBSTITUTE("~"&$A2&"~","~","#",COLUMN()))-FIND("#",SUBSTITUTE("~"&$A2,"~","#",COLUMN()-1))-1)
0
 
LVL 1

Author Comment

by:Tom F
ID: 39723977
Thanks I'll give these two formulas a try.  I know about text to columns - not what I was looking for.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39726315
Dare I ask why text to columns is not what you are looking for?

This function splits a string into component parts using a nominated separator which is exactly what it seems you are trying to do.

Thanks
Rob H
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 39726332
See attached, created using Text to Columns.

Calcs sheet generates strings of characters with random number of parts in each.

Copy and pasted values into values sheet, at top and below.

Then used the Text to Column function on bottom set.

Really quick, no need for messy formulas or VBA.

Thanks
Rob H
Text-to-columns.xlsx
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

730 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