Split text string using mid-search?

[DubboPete]

Hi all,

I have a string in a cell, which I have brought in without separating
out each set of characters, as I want to split the text into two
cells, but from hitting a specific string of text within the main
string.

I am looking for the instance "MRG" which occurs in different
positions in each string entry. I want to create two cells (like
text to columns) but split from the point immediately preceding
"MRG". Here's examples of what I have, and what I want to create:

Examples

2092^219^S204^MRG,2703255^
2093^244^S204^343^S201^MRG,5733401^

The result I am after:
Cell A1 Cell B1
2092^219^S204 MRG^2703255^
2093^244^S204^343^S201 MRG 5733401^

Hope that makes sense, and can it be done?

cheers
DubboPete

 

[Dave Peterson]

=search("^mrg",a1)
will find the position of that set of characters.

So you can use:
=left(a1,search("^mrg",a1)-1)
to get the left hand cell

And
=mid(a1,search("^mrg",a1)+1,255)
to get the right hand side
(255 is just a big number that's larger than the largest string size)

Another option is to copy the original data into two columns.

Then select the first column:
Edit|replace
what: ^mrg*
with: (leave blank)
replace all

Then select the second column
edit|replace
what: *^mrg
with: MRG
replace all

 

[Don Guillett]

Although I would use a macro,here is a formula solution
=LEFT(J18,SEARCH("mrg",J18)-2)
and
=RIGHT(J18,LEN(J18)-SEARCH("mrg",J18)+1)

 

[L. Howard Kittle]

Try these:
=LEFT(A10,FIND("^M",A10)-1)
=MID(A10,FIND("^M",A10)+1,99)

HTH
Regards,
Howard