How to split a string based on a sub string

[dippa]

Good day all,

I have a string say 'the code of the safe is x/o 123456 can you open it'

My challenge is to split the string into 3 parts
'the code of the safe is '
'x/o 123456'
' can you open it'

The middle part could also be 'X\o 147856783' or x \ o 12756', this slight variations but always in the form of 'x' followed by as '\' followed by a 'o' and then a number string.

Any idea on how to do this?

thanks all

[mcmahobt]

Does the phrase always begin with "The code of the safe is" and dows it always end with "Can you open it"?

[dippa]

Important point you made there, no it will be different in most cases, the only constant if I can put it that way will be the sequence of 'x' + '\' + 'o'.

[FDibbins]

Can you provide a list of all possible variations on x/o please?

And, what determines the end of teh 2nd phrase?

[mcmahobt]

Is it a forwardslash or a backslash?

[Butcher1]

The text to columns feature under the data tab should work, you will possibly have to use the CONCATENATE function afterwards to get it perfectly organized.

[dippa]

If not case sensitive
x \o
x\o
x\ o
x \ o

If case sensitive then include below
X \o
x \O
X \ O
X\O
X \ O
X \O
X\ O

Thanks

[FDibbins]

Your example in post 1 is x/o...so is it / or \ ?

[AlKey]

Maybe this

Just follow this order

in B1

=LEFT(A1,SEARCH("x",A1)-1)

In D1

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,SEARCH("x",A1)-1),"")," ",REPT(" ",255),2),255,255))

in C1

=SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,SEARCH("x",A1)-1),""),D1,"")

Row\Col	A	B	C	D
1	the code of the safe is x/o 123456 can you open it	the code of the safe is	x/o 123456	can you open it

[MarvinP]

Hi dippa and welcome to the forum,

See if
=MID(A1,FIND(UPPER("x"),UPPER(A1)),FIND(" ",A1,FIND(UPPER("o"),UPPER(A1),FIND("\",A1))+2)-FIND(UPPER("x"),UPPER(A1)))
works for you. See the attached example.

[dippa]

Hi Mate '/'

thanks

[dippa]

Hi Mate '/'

thanks

[dippa]

thanks guys, both work well enough for me to do what I need to do, would still be interested if there are more solutions.

[miloki]

Greetings.

Looks like Marvin and AlKey submitted good answers for you, but personally i like having multiple ways forward in case the situation changes on the spreadsheet side. Try these to see if they work for you:

Assumptions:
*I assume your text lies in column A
*I assume the format is constant: 'the code of the safe is **####### can you open it' with the only variety being the actual combination or the style of the x/o (x\o, X/O or any other format will work fine)
*I assume you have columns B thru D available for formulas. if you do not, I recommend inserting blank columns there, or referencing you full string on another sheet and doing the formula work there
*I assume this sheet is not protected beyond your ability to modify. If you do not have rights to change things and add formulas, you will need to do this work in another workbook and add it in somewhere/somehow else




In column B, top cell adjacent to your data (assumed as B1): =LEFT(A1, FIND("s ",A1))
In column C same row as previous: =MID(A1,FIND("s ",A1)+2,FIND(" ca",A1)-FIND("s ",A1)-2)
In column D, same row as previous: =MID(A1,FIND(" ca",A1)+1,LEN(A1))

This should break that string along the words before and after the blank on either side of the combination substring, there for allowing for subtle changes while retaining function. If you cahnge the wording so the word 'is' and 'can' are not those bookend words, you can swap the new letter out in the formulas and it will still work. As said before, anything inbetween these two bookends will get cut and placed into the middle cell.

Good luck!

Mike

[dippa]

MarvinP,
fyi
"the code x of the safe is x\O 123456 can you open it" Restults in "x of the safe is x\O 123456"
thanks again

[dippa]

I can force my users to enter a constant sting of "w/o", would simplify it a little?

[miloki]

I am confused by the 'w/o'...and it can be hard to change users behavoir in excel, especially if they are undertrained. I try to code around their habits.

With the setup in my post, the only necessary parts are the words is and can. "************is &&&&&&& can **********" will give you:

*********is

&&&&&&&&&

can ********

in three separate cells.

Happy Excelling

[AlKey]

I think that taking different approach may be somewhat a better option. Instead of extracting the middle part, use formula to extract numbers only and add "x/o " at the beginning. This seems to a more reliable way of doing that and you can avoid any inconsistencies with X/ or X / and so one.

Try this one

="x/o "&LOOKUP(25^25,0+MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),ROW($1:$10000)))

[FDibbins]

miloki, perhaps you missed this part from posts 2 & 3?

Does the phrase always begin with "The code of the safe is" and dows it always end with "Can you open it"?

Important point you made there, no it will be different in most cases, the only constant if I can put it that way will be the sequence of 'x' + '\' + 'o'.

So there is no guarantee that you will be able to find "s "

[MarvinP]

Here is an easier formula that keys off the "\".

See if it works better for you.

=MID(A1,SEARCH("\",A1)-3,SEARCH(" ",A1,SEARCH("\",A1)+5)-SEARCH("\",A1)+3)