Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Need formula to parse ~ at varied points

Posted on 2013-12-16
9
Medium Priority
?
173 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
  • 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 34

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 2000 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 34

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 34

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

New style of hardware planning for Microsoft Exchange server.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

916 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