Deconcatenating a list of strings

[Pallando_II]

My problem involves deconcatenating a list of strings (filenames) that all have the following structure:

ABD DEF-G.HIJ.ext

I'd like the final format to at least split the string on either side of the hyphen, and remove or separate the .ext component.

If the 'bar' symbol:'|' is a cell separator in the spreadsheet, the deconcatenated list (of ~350) filenames would have this structure in the spreadsheet:

| ABC DEF | - | G.HIJ | .ext | Please see attachment for a clearer image.

I have done similar string splits when I was working with Excel every day, but with unuse, have forgotten some details.

[protonLeah]

(How to) Upload a Workbook directly to the Forum
(please do not post pictures or links to worksheets)

• Click Advanced next to Quick Post button at the bottom right of the editor box.
• Scroll down until you see "Manage Attachments",
• Click the "Choose" button at the upper left (upload from your computer).
• Select your file, click "open", click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Close the Attachment Manager window.
• Click "Submit Reply"

Note: Please do not attach password protected workbooks/worksheets
Ensure to disable any Workbook Open/Autorun macros before attaching!

[David A Coop]

You could try the following four formulas - assuming your data is in cell A1.

B1 =LEFT($A1,FIND("-",$A1)-1)
C1 =MID(A1,FIND("-",$A1),1)
D1 =MID(A1,FIND("-",$A1)+1,FIND(".ext",$A1)-(FIND("-",$A1)+1))
E1 =RIGHT($A1,4)

There may be many other ways, but these work on the limited sample base you provided.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

[FDibbins]

Hi, welcome to the forum

It is always better if you give more than 1 example of what you are dealing with.

I'd like the final format to at least split the string on either side of the hyphen

That kinda tells me you want this split further?

[Pallando_II]

David:
Your formulae worked excellently on the one "ABC DEF..." filename I put in A1. The problem is that I have about 350 of these filenames to deconcatenate or split apart. I didn't say in my original post that I don't need the extension, just the strings to the immediate left and right of the hyphen (but not the hyphen itself). If my screenshot came through they would be just TITLE and AUTHOR.

I've not been able to configure your formulae in a way that would separate a column of 350 of these filenames. I'm going to attempt to attach a worksheet with 40 filenames. I omitted the extension because I could easily do so and I don't need it. And hopefully save a step.

Thanks for your help,
Pallando

[newdoverman]

I think that there is a mistake in entry on row 8 that has --. Without that, this should work in B1 and fill down

Please Login or Register  to view this content.

Enter in C1 and fill down

Please Login or Register  to view this content.

[Phuocam]

Or try this ...

Formula in B1:

=LEFT(A1,LEN(A1)-LEN(C1)-1)

Formula in C1:

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)),255))

[Pallando_II]

Thanks, Newdoverman. I thought I knew Excel reasonably well at one time (90's, 00's), but I couldn't even follow your logic on this one. If you have the time and inclination, could you give me a nickel's explanation of your algorithm?

[newdoverman]

The process that I followed is as follows. There were instances in your list with more than one - . I think that the double -- is a mistake so didn't make an effort to take care of that.
The first part of the first formula "IF(LEN(SUBSTITUTE(A1,"-"," "))-LEN(A1)=1" tests to see if there is more than one - in the string. The substitute replaces the - with two spaces. If the length of the substitution is longer than the original contents of A1 by 1 then there was only 1 - in the string. If the difference is 1 then the TRUE part of the IF statement "LEFT(A1,FIND("-",A1)-1)" is calculated. This simply searches for the - and returns the characters to the left of the -. The -1 eliminates the - from the result.

If the result of "IF(LEN(SUBSTITUTE(A1,"-"," "))-LEN(A1)=1" is greater than 1 the FALSE part of the IF statement "LEFT(SUBSTITUTE(A1,"-","~",2),FIND("~",SUBSTITUTE(A1,"-","~",2))-1))" comes into play. The SUBSTITUTE with the ~ replaces the - with a ~. This will force the second - in the string to be used in the FIND so that the second - determines what the LEFT function will return.

Seeing that your examples had a maximum of two - characters I did not make any accommodation for more than that. I have no idea how many different arrangements of - with and without spaces and other characters exist so just used your example as truly representative of your data.

Having separated the left part of the string, that value can be used to isolate the right side of the string. That is what the second formula does.

If you find that there are extraneous spaces, you can surround both formulae with the TRIM function to get rid of unnecessary spaces.

There may be methods that are more simple given but that is what I came up with in a limited amount of time.
I hope that this at least helps.

[deciog]

Pallando_II

See if this way helps

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))

Decio

[Pallando_II]

deciog

Your code works well. I had to figure that you only gave me one-half of what I needed since I could only get the right half of my files. "Try LEFT and see what happens. OK, there's the left side." Your code is more compact that some code presented and also seems to take care of any number of "-" 's which divide left from right, although any double hyphen like in my Row-8 is a mistake.

Thanks,
Pallando_II