Solved

Need formula to parse ~ at varied points

Posted on 2013-12-16
9
164 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 32

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 32

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 32

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

809 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