Mid Function

[spencerl]

I have data that was originally in three columns, that was then combined usins a concatenate formula. I used an additional formula to then remove all duplicates from that data, now I want to use another formula to seperate the data back into the three columns. I don't want to use "text to columns" because I want it to update as the data is updated.

Here's how the data looks;

DAVID BARTON`MIKES LOADING SERVICE, INC`242342

I would like "Dave Barton", "Mikes Loading Service, Inc" in their own columns...(I don't actually need the last string of characters at this point.

I'm having no problems getting the first string of characters using the following formula:
=LEFT(J2,FIND("`",J2,1)-1)

But, I'm stumped on the "mid"

Best regards,

[integra]

spencerl
it is tricky since your 1st group name length is not fixed. you will have to find the 2nd group name to apply the "MID" function.
assume your data is in column L and you "don't actually need the last string of characters", try this:
=SUBSTITUTE(MID(L11,FIND("`",L11,1),LEN(L11)-FIND("`",L11)+1),"/","",1)
good luck

[FDibbins]

for the middle part of your requirement, try...
=MID(A9,FIND("`",A9,1)+1,FIND("`",A9,FIND("`",A9,1)+1)-FIND("`",A9,1)-1)
for the last part, try...
=RIGHT(A9,LEN(A9)-FIND("`",A9,FIND("`",A9,1)+1))

[spencerl]

spencerl
it is tricky since your 1st group name length is not fixed. you will have to find the 2nd group name to apply the "MID" function.
assume your data is in column L and you "don't actually need the last string of characters", try this:
=SUBSTITUTE(MID(L11,FIND("`",L11,1),LEN(L11)-FIND("`",L11)+1),"/","",1)
good luck

Great thank you, it worked not I just need to trim off the beginning ` and the last string of characters.