Solved

Need formula to parse ~ at varied points

Posted on 2013-12-16
9
160 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
Comment Utility
Have you tried

Data > Text-to-columns
0
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
This will do it, but it's ugly as hell.
EE---split-text-string.xlsx
0
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
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
 
LVL 23

Expert Comment

by:DanCh99
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks I'll give these two formulas a try.  I know about text to columns - not what I was looking for.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

9 Experts available now in Live!

Get 1:1 Help Now