Any way possible to parse this text??

[jillbee]

We received a large Excel file that contains text in one field that's useless as entered. What we've been trying to do, without much success(!), is create formula(s) to break the text down into separate fields.

For example, in the attached "sample.xls" file, Column A has the raw data. We need to break Column A into three separate fields as shown in Columns B, C, and D. For illustration purposes I've just typed the broken down text in the first two rows to show how we'd like it to appear.

Given there's no hard & fast convention for how the data was entered initially (other than the first "/" between the names, even coming close and having to tweak manually would be a huge help.

Any suggestions would be greatly appreciated.

Jill

[Ace_XL]

In B2
=LEFT(A2,FIND("/",A2)-1)

In C2
=LEFT(SUBSTITUTE(A2,E2&"/",""),FIND("(",SUBSTITUTE(A2,E2&"/",""))-1)

In D2
=RIGHT(A2,LEN(A2)-FIND("(",A2)+1)

Copy down

[:) Sixthsense :)]

In B2 Cell

=IFERROR(LEFT(A2,FIND("/",A2)-1),"")

In C2 Cell

=IFERROR(MID(A2,FIND("/",A2)+1,(IFERROR(FIND("(",A2),LEN(A2)+1)-FIND("/",A2))-1),"")

In D2 Cell

=IFERROR(MID(A2,FIND("(",A2),5^5),"")

Drag all the formula's down...

[FDibbins]

Slightly different approach....
1st part...=LEFT(A2,FIND("/",A2,1)-1)
middle part...=IF(ISERROR(FIND("(",A2,1)),MID(A2,FIND("/",A2,1)+1,99),MID(A2,FIND("/",A2,1)+1,FIND("(",A2,1)-FIND("/",A2,1)-2))
last part...=IF(ISERROR(FIND("(",A2,1)),"",MID(A2,FIND("(",A2,1),99))

I notice that the 2nd entry does not have a 3rd part, so my 2nd and 3rd formulas adjust to cater for that

[jillbee]

WOW-- 3 responses just minutes after my initial post!
Thanks very much to all who replied.

Your formulas worked great Ace_XL. Where there was no paren text at the end of A3 that was noted but that's not an issue at all.

In B2
=LEFT(A2,FIND("/",A2)-1)

In C2
=LEFT(SUBSTITUTE(A2,E2&"/",""),FIND("(",SUBSTITUTE(A2,E2&"/",""))-1)

In D2
=RIGHT(A2,LEN(A2)-FIND("(",A2)+1)

Copy down

[jillbee]

Great-- works perfectly! Thank you SO much. Perhaps I'll tell the others that I was up all night working on this.

Nah, got to give credit where credit's due. I wasn't even close.

In B2 Cell

=IFERROR(LEFT(A2,FIND("/",A2)-1),"")

In C2 Cell

=IFERROR(MID(A2,FIND("/",A2)+1,(IFERROR(FIND("(",A2),LEN(A2)+1)-FIND("/",A2))-1),"")

In D2 Cell

=IFERROR(MID(A2,FIND("(",A2),5^5),"")

Drag all the formula's down...

[jillbee]

Thanks much FDibbins. However, unless I messed it up somehow, the "last part' produced TRUE or FALSE rather than the paren text. I see you were allowing for a field that didn't have that text at the end, but somehow I couldn't get it to work. (?)

Slightly different approach....
1st part...=LEFT(A2,FIND("/",A2,1)-1)
middle part...=IF(ISERROR(FIND("(",A2,1)),MID(A2,FIND("/",A2,1)+1,99),MID(A2,FIND("/",A2,1)+1,FIND("(",A2,1)-FIND("/",A2,1)-2))
last part...=IF(ISERROR(FIND("(",A2,1)),"",MID(A2,FIND("(",A2,1),99))

I notice that the 2nd entry does not have a 3rd part, so my 2nd and 3rd formulas adjust to cater for that

[FDibbins]

take a look at the attached

[jillbee]

Got it-- works GREAT! Many thanks.

take a look at the attached

[FDibbins]

Happy to help and thanks for the feedback

For future reference, if your question has been answered, please take a moment to mark the thread as "solved" by using the THREAD TOOLS towards the top of the screen- it helps keep things neat and tidy lol. I will do it for you this time L(

[jillbee]

Ah, "Thread Tools". I did want to mark as solved but I was looking for that option in the message box itself. Didn't notice that menu up there. Thanks again.

Happy to help and thanks for the feedback

For future reference, if your question has been answered, please take a moment to mark the thread as "solved" by using the THREAD TOOLS towards the top of the screen- it helps keep things neat and tidy lol. I will do it for you this time L(