Splitting text with Tilde between them

[Jill Pleau]

Hello, we have security groups that are separated by a tilde. We would like to separate them into columns for analysis. I've tried a few things, but can figure out how to get past the first tilde. Any thoughts would be greatly appreciated.

Insp.adm~Insp~InspShip~job.adm~ord.adm~prod.ctl~pc.adm~pur~pur.pm~rec~sls.adm~sh.sup


Jill

[Ace_XL]

Try this

Select your range

Data -- Text to Columns -- Delimited -- Other (Type in ~) -- Finish

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[gue2013]

look to
https://support.office.com/en-us/art...a-7a3e9c363ed7

[hrlngrv]

Someone ought to provide a formula-based approach. With your string in cell E11,

E13: =LEFT(E11,FIND("~",E11&"~")-1)
F13: =MID(LEFT($E$11,FIND(CHAR(127),SUBSTITUTE($E$11&"~","~",CHAR(127),COLUMNS($E13:F13)))-1),SUMPRODUCT(LEN($E13:E13)+1)+1,255)

Fill F13 right as far as needed. Or the more efficient 2 cells/result approach

E15: =LEFT($E$11,FIND("~",$E$11&"~")-1)
E16: =LEN(E15)+2

F15: =MID($E$11,E16,FIND("~",$E$11&"~",E16)-E16)
F16: =E16+LEN(F15)+1

Select F15:F16 and fill right as far as needed.

[Anh.Normus]

I love formula-based approach too because it is scalable. But I am specialized in designs I am going to get to the designs of it.

column 001: your original text
columns 003 to 502: using find gets the character position for the next tilde (column 003 uses column 002 as reference)
columns 504 to 1003: using the numbers from column offset -502 and -501 determine the part of the string from column 001 to parse

Gimme a like if you can follow answer to get your own solution :D

[hrlngrv]

I love formula-based approach too because it is scalable. But I am specialized in designs I am going to get to the designs of it. . . .

When & if Excel finally gets regular expression support (something StarOffice Calc had more than 2 decades ago, and Google Sheets has had for almost a decade at this point), all this effort will be rendered unnecessary. From Google Sheets,

E17: =MID(REGEXEXTRACT("~"&$A1,"(~[^~]*){"&COLUMNS($A3:A3)&"}"),2,255)

with E17 filled right as far as needed.

[alansidman]

Here is another option. Power Query. Mcode below

Please Login or Register  to view this content.

[hrlngrv]

Here is another option. Power Query. . . .

#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", type text}, {"Column1.10", type text}, {"Column1.11", type text}, {"Column1.12", type text}})

I haven't spent enough time with PowerQuery. Is it really necessary to specify Column1.1 to Column1.12 separately?

[Rick Rothstein]

A single formula solution...

Assuming the text in cell A1 is less than 300 characters long (otherwise increase all 300's to a number larger than the maximum anticipated length of the text in cell A1), put this formula in cell B2 and copy it across for as many cells as you think you will ever need. Note though that you will want to keep this number low if possible as its size affects the maximum number of split out text can be distributed (if I remember correctly, using 1000 would limit the number of output columns to around 40).

=TRIM(MID(SUBSTITUTE("~"&$A1,"~",REPT(" ",300)),COLUMNS($B:B)*300,300))

[BMV]

the text in cell A1 is less than 300 characters long
=TRIM(MID(SUBSTITUTE("~"&$A1,"~",REPT(" ",300)),COLUMNS($B:B)*300,300))

For dynamic length of source string
=TRIM(MID(SUBSTITUTE("~"&$A1,"~",REPT(" ",LEN(A1))),COLUMNS($B:B)*LEN(A1),LEN(A1))
=TRIM(MID(SUBSTITUTE("~"&$A1,"~",REPT(" ",LEN(A1))),COLUMN(a1)*LEN(A1),LEN(A1))

[alansidman]

I haven't spent enough time with PowerQuery. Is it really necessary to specify Column1.1 to Column1.12 separately?

the columns are arbitrarily named by PQ. You can change the column names after the split takes place.

[Rick Rothstein]

For dynamic length of source string
=TRIM(MID(SUBSTITUTE("~"&$A1,"~",REPT(" ",LEN(A1))),COLUMNS($B:B)*LEN(A1),LEN(A1))
=TRIM(MID(SUBSTITUTE("~"&$A1,"~",REPT(" ",LEN(A1))),COLUMN(a1)*LEN(A1),LEN(A1))

I know I could have used the LEN function three times in place of the hard-coded numbers as you did, but I figure a person wanting to split delimited text usually has an idea of what the maximum length of that text might be. Also, I like to limit the number of function calls an Excel formula has to make if possible (not always, but that tends to make the formula more efficient).

[BMV]

but I figure a person wanting to split delimited text

Rick, I"m sre you know about Len and I only remark about this option.
However the second solution is

Please Login or Register  to view this content.

[hrlngrv]

the columns are arbitrarily named by PQ. You can change the column names after the split takes place.

You misunderstand my question, so I'll rephrase: there's no way in PowerQuery to DO FOR ALL COLUMNS? If there could be 12 separate fields, it's necessary to list all 12 fields/columns in the code? No explicit or (better) implicit looping constructs?

[alansidman]

The code is generated through the User Interface. In this case, you select the Icon in the menu to split the field and list the delimeter as tilde. The code is then generated by Power Query and lists the column headers according to the number of fields are required.

https://support.office.com/en-us/art...f-8e0da9539662

[Jill Pleau]

Thanks to all who replied - Ace_XL's solution solved my problem - and so simple.