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
Solved

Need formula to parse ~ at varied points

Posted on 2013-12-16
9
165 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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.

789 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