Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
Search for a text string | Excel Discussion (Misc queries) | |||
How to split number and text string to separate cells? | Excel Discussion (Misc queries) | |||
Search, find or lookup defined text in text string | Excel Worksheet Functions | |||
Newbie: How to search a text string from right | Excel Worksheet Functions |