Split text string using mid-search?
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 |
Split text string using mid-search?
=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 DubboPete wrote: 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 |
Split text string using mid-search?
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) -- Don Guillett Microsoft MVP Excel SalesAid Software "DubboPete" wrote in message ... 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 |
Split text string using mid-search?
Try these:
=LEFT(A10,FIND("^M",A10)-1) =MID(A10,FIND("^M",A10)+1,99) HTH Regards, Howard "DubboPete" wrote in message ... 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 |
All times are GMT +1. The time now is 12:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com